Search code examples
mysqlpentahokettleinsert-updatespoon

Insert auto-incremental id on Kettle/Spoon

I have a single column of "names" on a text file. And I want to add this column to my database's table "names" that already exists and has a lot of names. That's looked very simple, but I don't know how to add the auto-incremental ID

I have something like this:

names
John
Lars
Peter

I wanted something like this.

 id | names
 .........
 68 | John
 69 | Lars
 70 | Peter

This is how I create my table:

CREATE TABLE IF NOT EXISTS `names` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `name` VARCHAR(45) NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '')
ENGINE = InnoDB;

enter image description here

Solution

  • There are two details to take in consideration:

    1 - If you do not want two rows with the same name in the database. To accomplish that you must set only the name field in the lookup part of the insert/update task.

    2 - If you can have two rows with the same name. Do not put anything in the lookup part of the insert/update task.

    Kettle will not include the ID colummn in insert on both cases. Mysql will define the next ID automatically as the ID field is marked as auto_icrement.

    UPDATE

    Please, take a look in the target table field. You have defined the "domain" table instead of "names".