armen armen - 7 months ago 13
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

Use the OUTPUT clause (SQL2005 and up):

DECLARE @IDs TABLE(ID int)

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
WHEN NOT MATCHED THEN
    INSERT (Field1, Field2) VALUES(S.Field1, S.Field2)
OUTPUT inserted.ID, S.ID INTO @IDs(Table1ID, Table2ID)