Odd behaviour of SCOPE_IDENTITY with INSERT INTO ... OUTPUT ... INTO

Odd behaviour of SCOPE_IDENTITY with INSERT INTO ... OUTPUT ... INTO by Martin Smith

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	765809
Opened:	10/2/2012 3:43:21 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Repro has three different queries of the following form

insert into #t
output inserted.a into #inserted_into
values ('a'),('b');
select scope_identity()

If only #t has an IDENTITY column defined then scope_identity returns the ID from #t.

If both tables have an IDENTITY column defined then scope_identity returns the ID from #inserted_into

If only #inserted_into has an IDENTITY column defined then scope_identity returns NULL.

(Originally found http://stackoverflow.com/q/12698388/73226)

Details (expand)

English

SQL Server 2012 - Developer Edition

SQL Engine

Not Applicable

set nocount on;

create table #t ( a char(1), i int identity (50, 1));
create table #inserted_into ( a char(1), i int identity (400,1));

insert into #t
output inserted.a into #inserted_into
values ('a'),('b');

select scope_identity(), 'Both have identity'

DROP TABLE #t, #inserted_into

go

create table #t ( a char(1));
create table #inserted_into ( a char(1), i int identity (400,1));

insert into #t
output inserted.a into #inserted_into
values ('a'),('b');

select scope_identity(), 'just #inserted_into has identity'

DROP TABLE #t, #inserted_into

go

create table #t ( a char(1), i int identity (50, 1));
create table #inserted_into ( a char(1));

insert into #t
output inserted.a into #inserted_into
values ('a'),('b');

select scope_identity(), 'just #t has identity'

DROP TABLE #t, #inserted_into

Returns

--------------------------------------- ------------------
401                                     Both have identity


--------------------------------------- --------------------------------
NULL                                    just #inserted_into has identity


--------------------------------------- --------------------
51                                     just #t has identity

BOL says

@@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.

So I would have expected the first result to return 51

X64

0 attachments

Hello Martin,
We investigated the issue and found that changing the behavior is not an easy thing to do. It would basically require redefining some of the behavior when both INSERT & OUTPUT INTO target has identity columns.
Given the nature of the problem & the uncommon scenario, we have decided not to fix the issue.

--
Umachandar, SQL Programmability Team