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

sql 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

sql
SELECT @@identity, scope_identity()

If it doesn’t return 1, run the following

sql 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

sql
INSERT TestOutput 
OUTPUT inserted.id,inserted.SomeCol, @@identity, scope_identity() INTO TestOutput2
VALUES ('20120525')

If we do a select for both tables

sql SELECT * FROM TestOutput SELECT * FROM TestOutput2


We see the following

<pre>id	SomeCol
-----   ----------
1	2012-05-25</pre>

And here

<pre>id	ID2	SomeCol		IndentityVal	ScopeIdentityVal
----    -----   ------------    ------------    ----------------
1	1	2012-05-25	1		1</pre>

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

sql
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

sql INSERT TestOutput OUTPUT inserted.id,inserted.SomeCol, INTO TestOutput2 VALUES (‘20120526’)


Let's see what we have

sql
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

sql SELECT @@identity, scope_identity()


The output of that is what will be inserted when you run the query below next

sql
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

sql CREATE TABLE TestOutput3 (id int identity (1000,1) not null,SomeCol date) GO

INSERT TestOutput3 VALUES(‘20120101’)

SELECT * FROM TestOutput3


Output

<pre>id	SomeCol
----    -----------
1000	2012-01-01</pre>

Now when you run this again, it will insert the value 1000

sql
INSERT TestOutput 
OUTPUT inserted.id,inserted.SomeCol, @@identity, scope_identity() INTO TestOutput2
VALUES ('20120525')

sql 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