How to Efficiently Get Identity Values After Inserting Multiple Records in SQL Server?

Insert Multiple Records and Obtain Identity Value without Cursor

Inserting multiple records into a parent table (A) from a child table (B) is a common task in data management. Often, the need arises to update the child table's records with the identity values generated for the newly inserted records in the parent table.

Microsoft SQL Server 2005 provides a solution to this requirement through the OUTPUT clause. The OUTPUT clause captures the identity values of the newly inserted records and stores them in a table variable (@output). This allows for efficient retrieval of the identity values without the overhead of using a cursor.

Here is an example code snippet demonstrating the usage of the OUTPUT clause:

DECLARE @output TABLE (id int)

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

-- Retrieve the identity values from @output
select * from @output

By executing the above code, the identity values of the inserted records will be stored in the @output table variable. This variable can then be used to update the NewId column in table B appropriately.

Using the OUTPUT clause is an efficient and straightforward approach for inserting multiple records and obtaining their identity values in Microsoft SQL Server 2005. It provides a robust and maintainable solution for data manipulation tasks involving parent and child tables.

The above is the detailed content of How to Efficiently Get Identity Values After Inserting Multiple Records in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!