Toreador (5/13/2010)
Interesting - it never occurred to me that the identity values wouldn't be available in the inserted table!So how would you get the identity values of the rows you'd just inserted?
If you are using an instead of trigger, I'm not sure. However, not using an instead of trigger works fine to retrieve the identity values using OUTPUT. I ran the below on SQL Server 2008.
CREATE TABLE Test
(
ID int identity(1,1) NOT NULL PRIMARY KEY,
AnotherColumn varchar(50) NULL
)
GO
CREATE TRIGGER Test_Insert
ON Test
AFTER INSERT
AS
SELECT 'Trigger Executed'
GO
DECLARE @tTableVar table(TestID int NOT NULL,
TestAnotherColumn varchar(50) NULL)
INSERT INTO Test(AnotherColumn)
OUTPUT inserted.ID, inserted.AnotherColumn
INTO @tTableVar(TestID,TestAnotherColumn)
SELECT 'Blah'
UNION ALL
SELECT 'Blah2'
SELECT TestID,TestAnotherColumn FROM @tTableVar
DROP TABLE Test