One Star

Insert a row, then get the primary identity key for use a foreign key.

Hello All,
I have been bashing my head against this one for a while and I am not coming up with any good solutions. I am performing an extract, transform and load of a legacy database into a new schema. The DB folks chose to build a OO type schema where there is a Parent table with a primary identity key and the child table has a primary key that is also a foreign key containing the identity key of the parent table. So, upon extraction, I need to create a row in the parent table, get the primary key that is auto generated by the identity property in the parent table, then use that key as the primary key as the child table when I map the child table attributes.
Does anyone have suggestions here? I have started thrashing and am no longer making progress.
Thanks,
Jim Long
10 REPLIES

Re: Insert a row, then get the primary identity key for use a foreign key.

if you are using mysql as your database you can use tMysqlLastInsertId to retrieve the last autoincrement key that was created on that connection.
this can be found under Databases->MySQL
edit:spelling
One Star

Re: Insert a row, then get the primary identity key for use a foreign key.

Alas, I am using SQL Server 2008.
Any ideas on how I would do this in that platform?
One Star

Re: Insert a row, then get the primary identity key for use a foreign key.

I found tMSSQLServerLastInsertId.
How do I use this little bugger?
Thanks,
Jim
One Star

Re: Insert a row, then get the primary identity key for use a foreign key.

hello @ all
i've got the same problem .......but with postgres.......there is no lastID for Talend
any solutions?
One Star

Re: Insert a row, then get the primary identity key for use a foreign key.

Hi,
you may use a tPostgresInput with something like "SELECT MAX(id) FROM table".
To give you some more information a screen shot of your job would be nice.
Bye
Volker
One Star

Re: Insert a row, then get the primary identity key for use a foreign key.

Hello,
I am still working on this problem. I am using MS Sql Server 2008 so I have tried using tMSSqlLastInsertID. When I run my job, here is what I get:
Starting job DataFlexDump at 14:45 12/03/2009.
connecting to socket on port 5285
Exception in component tMSSqlLastInsertId_1
java.lang.NullPointerException
at kaptrasnformation.dataflexdump_0_1.DataFlexDump.tDBInput_1Process(DataFlexDump.java:1723)
at kaptrasnformation.dataflexdump_0_1.DataFlexDump.runJobInTOS(DataFlexDump.java:2784)
at kaptrasnformation.dataflexdump_0_1.DataFlexDump.main(DataFlexDump.java:2690)
connected
disconnected
Job DataFlexDump ended at 14:45 12/03/2009.
Here is a screenshot:
Community Manager

Re: Insert a row, then get the primary identity key for use a foreign key.

Hello jnltech
You don't have a correct way of using tMSSqlLastInsertID componnet, please have a look at 4895 and learn its usage.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Insert a row, then get the primary identity key for use a foreign key.

Hello Shong,
I do not understand the use of OnSubjobOk. How does this come about?
I will keep digging around in the forums and tutorials to see if I can discover more about this, but any pointers would be greatly appreciated.
Thanks,
Jim Long
One Star

Re: Insert a row, then get the primary identity key for use a foreign key.

Hi Jim,
Were you able to find the solution of your problem. I am asking this because I am facing the same problem when trying to insert data in child job and sharing the database connection between the parent and child job.
Any help will be highly appreciated.
Thanks,
Priyanka
Community Manager

Re: Insert a row, then get the primary identity key for use a foreign key.

Hi Jim,
Were you able to find the solution of your problem. I am asking this because I am facing the same problem when trying to insert data in child job and sharing the database connection between the parent and child job.
Any help will be highly appreciated.
Thanks,
Priyanka

Hi pbhat2
This topic was rather old, in order to better follow up your topic, can you please report a new topic with details for problem.
Thank you!
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business