SQL OUTPUT Clause

Technology: Microsoft SQL Server 2008 R2

Problem: I have a query which inserts multiple new records into an existing table. I need to retrieve the primary key IDs for these new records once they are inserted. I cannot use SCOPE_IDENTITY() as this returns only the last identity value inserted into an identity column.

The solution is to use the SQL OUTPUT clause. This clause has access to the INSERTED and DELETED virtual tables. In my scenario, I used OUTPUT with the INSERT clause, but it can also be used with UPDATE, or DELETE to identify the rows affected by these statements.

Firstly, I created a table variable to store off the new IDs for the inserted records. Note that you could also use a standard table or a temp table. Table variable worked best in my case.

DECLARE @NewIDs TABLE (Id int, CustomerName varchar(50))

I can then proceed to do my INSERTS via some SQL...

INSERT MyDestTable (CustomerName, Description, CreatedBy, CreatedDate)
OUTPUT Inserted.ID, Inserted.CustomerName INTO @NewIDs 
SELECT CustomerName, Description, SYSTEM_USER, GETDATE()
FROM MySourceTable 

The above query will select all records from 'MySourceTable' and insert them into 'MyDestTable.' (I've kept the select simple here, but in a real world scenario we would probably want to add a where clause or some joins to pull back only the records we need to insert, instead of the whole dataset). By adding one line between the INSERT and SELECT, I now have access to all the newly inserted records and their auto-assigned IDs:

SELECT Id, CustomerName FROM @NewIDs