can SQL insert using select return multiple identities?
lastinsertid, sql-server, sql-server-2008
Solution
You can use the output clause.
Something like this if your identity column is named `IdentityCol' will return you id's as a result set.
INSERT california_authors (au_id, au_lname, au_fname)
OUTPUT inserted.IdentityCol
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'
You can insert the id's to a table using `output ... into`. Here is a sample that stores the id's in a table variable.
declare @IDs table (id int)
INSERT california_authors (au_id, au_lname, au_fname)
OUTPUT inserted.IdentityCol INTO @IDs
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'
Problem
I am insertnig into a table using a selection ``` INSERT california_authors (au_id, au_lname, au_fname) SELECT au_id, au_lname, au_fname FROM authors WHERE State = 'CA' ``` say i have an identity column in `california_authors`. Can i get all the ids inserted by the above particular insertion just like i can get @@IDENTITY for last `single insertion` ? I can't use select command for `california_authors` as there may exists previously inserted records with filter `State = 'CA'`