Get IDs of multiple inserted records
-
I want to insert multiple records in a table, and after that, i want to retrieve the ID of the inserted records so i can use these ID's for further processing.
Example situation:
-table Keywords with columns ID, Name (unique)
-tempTable #NewKeywords with columns IDKeyword, Name
The IDKeyword column of the #NewKeywords is empty at the begining and should be filled with the ID of the keyword table. After all IDKeywords are filled in further processing is done (but this is out of the scope of this post)
First the IDKeyword of #NewKeywords is filled with existing Keywords
UPDATE #NewKeywordsSET IDKeyword = kwd.ID
FROM #NewKeywords new
JOIN Keywords kwd ON kwd.Name = new.Name
After this, there can be some records in the #NewKeywords without an IDKeyword since the Keyword doesn't exists yet. So these keywords are created:
INSERT INTO Keywords (Name)SELECT Name
FROM #NewKeywords
WHERE IDKeyword is null
Now we want to retrieve the ID's of the keywords we just inserted so we can update de #NewKeywords table:
UPDATE #NewKeywordsSET IDKeyword = kwd.ID
FROM #NewKeywords new
JOIN Keywords kwd ON kwd.Name = new.Name
WHERE new.IDKeyword IS NULL
As you can see, this is not very efficient, if i had to INSERT 1 record i would use the Scope_Identity to retrieve the ID of the record i just inserted. But with multiple records this doesn't work.
Does anybody have some sort of smart solution to make this a little bit more efficient??
Note: in this example the last update with join is relative lightweighted, but in real world this is quite an expensive query.
-
Suggest you look at the OUTPUT clause
DECLARE @ids TABLE (Name VARCHAR(20), ID INT)INSERT INTO Keywords (Name)
OUTPUT inserted.* INTO @ids
SELECT Name
FROM #NewKeywords
WHERE IDKeyword is null
SELECT * FROM @ids
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks a lot, this is EXACTLY what i was looking for.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply