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