Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: How to insert data into a table with auto_increment id

  1. #1
    Join Date
    Aug 2008
    Posts
    3

    Red face How to insert data into a table with auto_increment id

    I am a MySQL user, and a Kettle newbie. I want to load a set of data by using Kettle – Spoon version 3.0.3 in my proof of concept project. I have a table input. I want to insert a row of data in a table call A, and get the auto increment id as a primary key. Use the primary key which I get from table A and get some different data in different data fields from a same table input, and then insert them into a table call B. I try to accomplish this task within a day, but I already spend a week on it, and still can’t accomplish this one. I will be very grateful if someone could shed some light on top of this matter.
    Last edited by pleung; 09-03-2008 at 11:39 AM.

  2. #2

    Default

    Hi pleung,
    it could help us helping you if you provide more than that description.
    Maybe you can post your transformation and tables/DDL you still have so far.

    So far:
    - you have a source table
    - your destination table A seems to be a dimension table (feed from source)
    - your destination table B could be a fact table (feed from source and ID from dimension A)
    Right?
    (EDIT: Maybe your want to use a "Add sequence" step instead of auto increment?)

    Christoph
    Last edited by christophWeißenborn; 09-03-2008 at 12:32 PM.

  3. #3
    Join Date
    Aug 2008
    Posts
    3

    Default

    I have a set of tables,
    src1 is a input data source,
    dest1 and dest2 are output data sources.
    Auto increment pid must have because it is a requirement. I can not use add sequence.

    CREATE TABLE `src1` (
    `src_id` int(45) NOT NULL default '0',
    `status` varchar(45) default NULL,
    `domain` varchar(45) default NULL,
    `name` varchar(96) default NULL,
    `url` varchar(250) default NULL,
    `email` varchar(100) default NULL,
    `type` varchar(300) default NULL,
    `desc` varchar(2500) default NULL,
    PRIMARY KEY (`song_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    CREATE TABLE `dest1` (
    `pid` int(10) UNSIGNED NOT NULL auto_increment,
    `src_id` int(45) NOT NULL default '0',
    `name` varchar(96) default NULL,
    `url` varchar(250) default NULL,
    `email` varchar(100) default NULL,
    PRIMARY KEY (`pid`)
    ) ENGINE=Innodb DEFAULT CHARSET=latin1;

    CREATE TABLE `dest2` (
    `fk_id` int(10) UNSIGNED NOT NULL default '0',
    `status` varchar(45) default NULL,
    `domain` varchar(45) default NULL,
    `type` varchar(300) default NULL,
    `desc` varchar(2500) default NULL,
    PRIMARY KEY (`fk_id`)
    ) ENGINE=Innodb DEFAULT CHARSET=latin1;

    What I try to accomplish is listing in the following
    1) insert into dest1 ( src_id , name, url, email )
    select src_id , name, url, email from src1;
    2) I need to get the LAST_INSERT_ID() from dest1, and use it in step 3.
    3) insert into dest2 ( fk_id, status, domain, type, desc )
    select current_id, status, domain, type, desc from src1;

    My question is how to do all the 3 steps.
    Thanks

  4. #4

    Wink

    damned, to easy to "google":
    http://www.google.de/search?q=kettle+last_insert_id
    first result answer your question:
    http://forums.pentaho.org/showthread.php?t=49443

  5. #5
    Join Date
    Aug 2008
    Posts
    3

    Default

    Thanks for your quick response for the step 1. How about the 2 and 3?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.