I think I already know the answer but...
Given this statement:
Insert Into Allergy (Name, Category, Composition)
Select Name, Category, Composition
From ETVault Where Strain In (6, 2, 4)
Is it possible to get the identities that were inserted?
I understand I can break this into multiple statements and capture scope_identity() after each but that's not what I'm asking.
The first way I thought of doing this is to use a AFTER INSERT TRIGGER on the table, capture the entire rowset inserted, and get the identities from there.
EDIT Of course, you can do this by capturing OUTPUT (doh)
http://msdn.microsoft.com/en-us/library/ms177564.aspx
Here is a working sample, Since you hadn't provided sample data, I created my own Allery and ETVault tables, so use the example in a brand new database.
DROP TABLE Allergy
GO
CREATE TABLE Allergy
(
ID INTEGER IDENTITY (1, 1), NAME VarChar (20), Category VarChar (20), Composition VarChar (20)
)
GO
DROP TABLE ETVault
Go
CREATE TABLE ETVault
(
ID INTEGER IDENTITY (1, 1), Strain INT, NAME VarChar (20), Category VarChar (20), Composition VarChar (20)
)
GO
INSERT INTO dbo.ETVault
( Strain, NAME, Category, Composition )
VALUES
( 1, '1', '1', '1'),
( 2, '1', '1', '1'),
( 3, '1', '1', '1'),
( 4, '1', '1', '1'),
( 5, '1', '1', '1'),
( 6, '1', '1', '1'),
( 7, '1', '1', '1'),
( 8, '1', '1', '1')
DECLARE @CaptureTable TABLE (ID INT)
INSERT INTO dbo.Allergy
( NAME, Category, Composition )
OUTPUT INSERTED.Id INTO @CaptureTable
SELECT dbo.ETVault.NAME, Category, Composition
FROM dbo.ETVault
Where Strain In (6, 2, 4)
SELECT * FROM @CaptureTable
It gives you the output
(8 row(s) affected)
(3 row(s) affected)
ID
-----------
1
2
3
(3 row(s) affected)
