SQL Server list of insert identities

Question

I have a table with an autoincrement id that I am doing a

INSERT INTO ( ... ) SELECT ... FROM ...

Is there a way for me to get the list of id's that have been inserted?

I was thinking I could get the max id before the insert then after and assuming everything in between is new, but then if a row gets inserted from somewhere else I could run into problems. Is there a proper way to do this?

I am using SQL Server 2005

Answer 1

Create a table variable and then use the OUTPUT clause into the table variable.

OUTPUT inserted.NameOfYourColumnId INTO tableVariable

Then you can SELECT from your table variable.

Answer 2

Use the output clause.

DECLARE @InsertedIDs table(ID int);

INSERT INTO YourTable
    OUTPUT INSERTED.ID
        INTO @InsertedIDs 
    SELECT ...