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'
The INSERT statement lets you add one or more rows to a table or view in a SQL Server database. The statement is one of the primary data modification language (DML) statements available in Transact-SQL, along with UPDATE, MERGE, and DELETE.
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With