0

I'm in the process of writing batch loading SQL script and I would like to store the generated primary key of an insert statement into a temp variable and use it to reference the foreign key in the other tables. Any ideas?

I'm doing the following:

Y = INSERT INTO X(.....)

INSERT INTO Z(...,Y,);
benRollag
  • 1,219
  • 4
  • 16
  • 21
user1022900
  • 13
  • 2
  • what is the actual SQL you are using? Are you getting an error? – Justin808 Nov 01 '11 at 01:17
  • Do you not have a unique natural key (unique key that is not the `id` auto-gen id)? Also, unless you are processing line-by-line, you can only store one (or some limited number) id at a time that way (assuming your version of DB2 supports declaring variables) - this may be slow. Other than that... look for _data-change-table-reference_, probably the `NEW TABLE(INSERT ....)` clause. – Clockwork-Muse Nov 01 '11 at 17:27

1 Answers1

-1

I know in SQL Server you can do:

INSERT INTO MyTable ....
SELECT @Foo = @@IDENTITY

INSERT INTO SomeOtherTable(... fk_col ...) VALUES (... @foo ...)
Dylan Smith
  • 22,069
  • 2
  • 47
  • 62