can SQL insert using select return multiple identities?

Question

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'

Answer 1

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'