How to Retrieve Identity Values After Inserting Multiple Records in SQL Server 2005 Without Cursors?

Insert Multiple Records and Retrieve Identity Values

When inserting multiple records into a table with an identity column, obtaining the newly generated identity values can be crucial for subsequent data manipulations. This article explores how to achieve this without utilizing a cursor in Microsoft SQL Server 2005.

Problem Description

Consider the following scenario: You have two tables, Table A with an identity column (id) and Table B, which contains related data. You want to insert records from Table B into Table A and obtain the respective identity values to update the NewId column in Table B.

Solution

SQL Server 2005 provides a convenient mechanism through the OUTPUT clause to retrieve generated identity values during insert operations. The following code demonstrates how to use this feature:

DECLARE @output TABLE (id int)

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

This query will insert records from Table B into Table A and store the identity values in a table variable called @output. The table variable @output will contain a single column (id) with the newly generated identity values, allowing you to retrieve and use them for further processing.

Example

Consider the example tables and data provided in the problem description. After executing the above code:

-- Insert records into Table A and retrieve identity values
DECLARE @output TABLE (id int)

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

-- Display the identity values in the table variable
SELECT * FROM @output

The result will be a table with the identity values of the inserted rows:

id
-------
1
2
3

These identity values can then be used to update the NewId column in Table B accordingly.

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