Geek Answers Handbook

How can I get the identifiers of the rows that were inserted through insert ... select?

I insert records through a query similar to this:

insert into tbl_xyz select field1 from tbl_abc

Now I would like to return the newly created identification values ​​of the inserted records. How to do this with a minimum amount of locking and maximum reliability?

+4
source share
4 answers

You can get this information using the OUTPUT clause.

You can display your information in a table or view the target temperature.

Here is an example:

 DECLARE @InsertedIDs TABLE (ID bigint) INSERT into DestTable (col1, col2, col3, col4) OUTPUT INSERTED.ID INTO @InsertedIDs SELECT col1, col2, col3, col4 FROM SourceTable

You can then query the InsertedID table for your inserted identifiers.

+19
source

@@ IDENTITY will return you the last inserted IDENTITY value, so you have two possible problems.

  • Beware of triggers executed when pasting into table_xyz, as this may change the value of @@ IDENTITY.

  • Does tbl_abc have multiple lines. If so, @@ IDENTITY will only return the last row id

Problem 1 can be resolved with SCOPE__IDENTITY () instead of @@ IDENTITY. Problem 2 is harder to solve. Does field1 in tbl_abc define a unique entry in tbl_xyz if you can reselect data from table_xyz with an identity column. There are other solutions using CURSORS, but they will be slow.

+2
source 
 SELECT @@IDENTITY

Here's how I did it before. Not sure if this will meet the second half of your post though.

EDIT
Also found this link, but not sure if this is the same ...
How to insert multiple records and get identification value?

+1
source

As far as I know, you cannot do this with direct SQL in the same script. But you can create an INSERT trigger. Now, I hate triggers, but this is one way to do this.

Depending on what you are trying to do, you can first insert rows into the temp or table, and handle the result set this way. We hope that there is a unique column that you can reference.

You can also lock the table, get the maximum key, insert your rows, and then get your maximum key again and make a range.

Trigger:

 --Use the Inserted table. This conaints all of the inserted rows. SELECT * FROM Inserted

Temp table:

 insert field1, unique_col into #temp from tbl_abc insert into tbl_xyz (field1, unique_col) select field1, unique_col from tbl_abc --This could be an update, or a cursor, or whatever you want to do SELECT * FROM tbl_xyz WHERE EXISTS (SELECT top 1 unique_col FROM #temp WHERE unique_col = tbl_xyz.unique_col)

Key Range:

 Declare @minkey as int, @maxkey as int BEGIN TRANS --You have to lock the table for this to work --key is the name of your identity column SELECT @minkey = MAX(key) FROM tbl_xyz insert into tbl_xyz select field1 from tbl_abc SELECT @maxkey = MAX(key) FROM tbl_xyz COMMIT Trans SELECT * FROM tbl_xyz WHERE key BETWEEN @minkey and @maxkey
+1
source

More articles:


All Articles