How Can I Retrieve Identity Values After Bulk Inserts in SQL Server 2005?

Getting Identity Values During Bulk Inserts in SQL Server 2005

When inserting multiple records from one table to another, it can be useful to obtain the identity values assigned to the newly inserted records. This allows for efficient updates to the source table with foreign keys referencing the target table.

Inserting Records and Retrieving Identity Values

In this case, we want to insert records from table B into table A and retrieve the identity values of the newly inserted records for updating field "NewId" in table B.

Using the OUTPUT Clause

MS SQL Server 2005 provides the OUTPUT clause that allows users to capture the values inserted during a data modification operation. In this case, we can use it as follows:

DECLARE @output TABLE (id int)

Insert into A (fname, lname)
OUTPUT inserted.ID INTO @output
SELECT fname, lname FROM B

select * from @output

By using the OUTPUT clause, we create a table variable @output to store the identity values. The inserted.ID refers to the identity value of the newly inserted record in table A.

After executing the insert statement, the @output table will contain the identity values corresponding to the rows inserted from table B. You can then use these values to update the NewId field in table B without the need for cursors.

The above is the detailed content of How Can I Retrieve Identity Values After Bulk Inserts in SQL Server 2005?. For more information, please follow other related articles on the PHP Chinese website!