armen armen - 2 years ago 64
SQL Question

SQL : Retrieve inserted row IDs array / table

i have the following statement:

INSERT INTO table1 (field1, FIELD2)
SELECT f1, f2 FROM table2 -- returns 20 rows

after insert i need to know the array/table of IDs generated in table1.ID which is INT IDENTITY

thanx in advance.

Answer Source

Use the OUTPUT clause (SQL2005 and up):


INSERT INTO table1(Field1, Field2)
OUTPUT inserted.ID into @IDs(ID)
SELECT Field1, Field2 FROM table2

If you want to know exactly which rows from table2 generated which ID in table1 (and Field1 and Field2 aren't enough to identify that), you'll need to use MERGE:

DECLARE @IDs TABLE(Table1ID int, Table2ID int)

MERGE table1 AS T
USING table2 AS S
ON 1=0
    INSERT (Field1, Field2) VALUES(S.Field1, S.Field2)
OUTPUT inserted.ID, S.ID INTO @IDs(Table1ID, Table2ID)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download