How Can I Efficiently Retrieve Identity Values After Inserting Multiple Records in SQL Server?

Inserting Multiple Records and Retrieving Identity Values

When inserting multiple records into a table, you may encounter the need to retrieve the generated identity values for each inserted record. In this case, where you are inserting records from table B into table A and wish to obtain the identity values of the inserted records, there is a solution that eliminates the need for cursors.

In SQL Server 2005, you can leverage the OUTPUT clause to accomplish this task. The OUTPUT clause allows you to specify a table variable that will receive the output from the INSERT statement, including the identity values of the newly inserted rows.

Here's an example:

DECLARE @output TABLE (id int)

INSERT INTO A (fname, lname)
OUTPUT INSERTED.ID INTO @output
SELECT fname, lname
FROM B

By using the OUTPUT clause, you can populate a table variable named "@output" with the identity values of the inserted records. You can then query this table variable to retrieve the values:

SELECT *
FROM @output

This approach allows you to efficiently insert multiple records and obtain their identity values without resorting to cursors or additional queries.

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