How to Retrieve Identity Values After Bulk Inserting Records in SQL Server without Cursors?

Bulk Insert Records and Retrieve Identity Values without Cursors

Inserting multiple records into a table is a common task in database programming. Particularly when copying data from one table to another, it is desirable to obtain the generated identity values for the new records. In this regard, Microsoft SQL Server 2005 provides an efficient solution using the OUTPUT clause.

Considering the provided code snippet, where data is inserted from table B into table A:

Create Table A (id int identity, Fname nvarchar(50), Lname nvarchar(50))
Create Table B (Fname nvarchar(50), Lname nvarchar(50), NewId int)
Insert into A(fname, lname)
SELECT fname, lname
FROM B

To retrieve the identity values of the newly inserted records in table A, we can employ the following technique:

Use the OUTPUT Clause

The OUTPUT clause allows us to retrieve the values generated during an insert, update, or delete operation. In this case, we can use it to capture the identity values of the inserted rows.

DECLARE @output TABLE (id int)

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

By creating a table variable (@output) and specifying the OUTPUT inserted.ID expression, we instruct the query to return the identity values of the inserted records into @output.

Querying the Table Variable

Once the insert operation is complete, we can query the @output table variable to obtain the identity values:

select * from @output

This will display a table with one column (id) containing the identity values of the newly inserted records. These values can then be used to update the corresponding records in table B, if desired.

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