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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 765809 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 10/2/2012 3:43:21 PM
Access Restriction Public

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)
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