Home Dashboard Directory Help
Search

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


Status: 

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


3
0
Sign in
to vote
Type: Bug
ID: 765809
Opened: 10/2/2012 3:43:21 PM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description


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
Sign in to post a comment.
Posted by Microsoft on 2/27/2013 at 2:18 PM
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
Posted by Microsoft on 10/9/2012 at 11:54 AM
Hello Martin,
Thanks for reporting the issue. We will take a look at it and get back to you on our findings.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.