Axel posted a post titled Catching the OUTPUT of your DML statements earlier today. I posted something about the OUPUT clause myself as part of my SQL Advent 2011 series here: SQL Advent 2011 Day 11: DML statements with the OUTPUT clause
Reading Axel’s post where he describes how he can use this instead of a trigger got me thinking about how @@identity and scope_identity() would behave. You all know that when you have an insert trigger that inserts into a table with an identity column that @@identity will give you the value of the table that the trigger inserted into, scope_identity() will give you the id of the table that the trigger fired from.
Let’s see what happens with the OUTPUT clause. First create these two simple tables
CREATE TABLE TestOutput (id int identity not null,SomeCol date)
GO
CREATE TABLE TestOutput2 (id int identity not null,ID2 INT, SomeCol DATE, IndentityVal INT, ScopeIdentityVal int)
GO
Before we start make sure that this returns 1
SELECT @@identity, scope_identity()
If it doesn’t return 1, run the following
TRUNCATE TABLE TestOutput
INSERT TestOutput VALUES('20120101')
TRUNCATE TABLE TestOutput
SELECT @@identity, scope_identity()
Now we are set to go, we are inserting one row
INSERT TestOutput
OUTPUT inserted.id,inserted.SomeCol, @@identity, scope_identity() INTO TestOutput2
VALUES ('20120525')
If we do a select for both tables
SELECT * FROM TestOutput
SELECT * FROM TestOutput2
We see the following
id SomeCol ----- ---------- 1 2012-05-25
And here
id ID2 SomeCol IndentityVal ScopeIdentityVal ---- ----- ------------ ------------ ---------------- 1 1 2012-05-25 1 1
Not very useful since all the values are 1 so we don’t know where @@identity and scope_identity() get their values from
Let’s just insert a bunch of rows in the TestOutput table
INSERT TestOutput VALUES('20120101')
INSERT TestOutput VALUES('20120101')
INSERT TestOutput VALUES('20120101')
INSERT TestOutput VALUES('20120101')
Now if the do the insert with the OUTPUT clause again, the values will be different
INSERT TestOutput
OUTPUT inserted.id,inserted.SomeCol, INTO TestOutput2
VALUES ('20120526')
Let’s see what we have
SELECT * FROM TestOutput
SELECT * FROM TestOutput2
Here is the output
id SomeCol --- ---------- 1 2012-05-25 2 2012-01-01 3 2012-01-01 4 2012-01-01 5 2012-01-01 6 2012-05-26
The max value is 6 in the TestOutput table
id ID2 SomeCol IndentityVal ScopeIdentityVal --- --- -------- -------------- ------------------ 1 1 2012-05-25 1 1 2 6 2012-05-26 5 5
In the TestOutput2 table we have the value 5
The output is 2 for both. Now you are probably asking yourself why 2 was not inserted but 5 into the output2 table
Run this statement now
SELECT @@identity, scope_identity()
The output of that is what will be inserted when you run the query below next
INSERT TestOutput
OUTPUT inserted.id,inserted.SomeCol, @@identity, scope_identity() INTO TestOutput2
VALUES ('20120525')
If you run that the value 3 will be inserted
Here is what Books On Line has to say about the population of @@identity and scope_identity()
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT return identity values generated only by the nested DML statement, and not those generated by the outer INSERT statement.
In other words when you use @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT when inserting into a table using the OUTPUT clause which then inserts into another table, the values are for the insert that happens from the output clause, in this case it is the ID from the output2 table
But as you can see @@identity and scope_identity() are actually populated with the statement that ran before the current statement, just be aware of that
Here is a quick test
CREATE TABLE TestOutput3 (id int identity (1000,1) not null,SomeCol date)
GO
INSERT TestOutput3 VALUES('20120101')
SELECT * FROM TestOutput3
Output
id SomeCol ---- ----------- 1000 2012-01-01
Now when you run this again, it will insert the value 1000
INSERT TestOutput
OUTPUT inserted.id,inserted.SomeCol, @@identity, scope_identity() INTO TestOutput2
VALUES ('20120525')
SELECT * FROM TestOutput2
id ID2 SomeCol IndentityVal ScopeIdentityVal ----- ----- --------- ------------ -------------------- 1 1 2012-05-25 1 1 2 6 2012-05-25 5 5 3 7 2012-05-25 2 2 4 8 2012-05-25 3 3 5 9 2012-05-25 1000 1000
If you do this now, you will get the value 5, the value 5 is the value of the identity column for the TestOutput2 table, we last inserted into this table via the OUTPUT clause
So be aware when using @@identity and scope_identity(), they might not behave as you would expect them to behave. The thing to remember is that @@identity and scope_identity() don’t return the values from the statement just executed but from the one that was just executed prior to the current statement. If you do use the OUTPUT clause, @@identity and scope_identity() will return the value from the table where the OUTPUT clause is inserting into. If you need the ID for the table that the OUTPUT clause inserted into, just grab @@identity or scope_identity() after the statement executed
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
