SQL Server 输出子句

我有点困惑为什么我似乎无法使用下面的语句获得插入行的“新身份”。SCOPE_IDENTITY()只是返回 null。

declare @WorkRequestQueueID int
declare @LastException nvarchar(MAX)
set @WorkRequestQueueID = 1
set @LastException = 'test'

set nocount off

DELETE dbo.WorkRequestQueue
OUTPUT
        DELETED.MessageEnvelope,
        DELETED.Attempts,
        @LastException,
        GetUtcdate(), -- WorkItemPoisened datetime
        DELETED.WorkItemReceived_UTC
    INTO dbo.FaildMessages
FROM dbo.WorkRequestQueue
WHERE
    WorkRequestQueue.ID = @WorkRequestQueueID

IF @@ROWCOUNT = 0 
  RAISERROR ('Record not found', 16, 1) 

SELECT Cast(SCOPE_IDENTITY() as int) 

任何帮助将不胜感激。

现在我使用这样的解决方法。

declare     @WorkRequestQueueID int
declare @LastException nvarchar(MAX)
set @WorkRequestQueueID = 7
set @LastException = 'test'

set nocount on
set xact_abort on 

DECLARE @Failed TABLE 
(
    MessageEnvelope xml, 
    Attempts smallint,
    LastException nvarchar(max),
    WorkItemPoisened_UTC datetime,
    WorkItemReceived_UTC datetime
)

BEGIN TRAN

DELETE dbo.WorkRequestQueue
OUTPUT
    DELETED.MessageEnvelope,
    DELETED.Attempts,
    @LastException,
    GetUtcdate(), -- WorkItemPoisened datetime
    DELETED.WorkItemReceived_UTC

INTO 
    @Failed
FROM 
    dbo.WorkRequestQueue
WHERE
    WorkRequestQueue.ID = @WorkRequestQueueID

IF @@ROWCOUNT = 0 BEGIN  
    RAISERROR ('Record not found', 16, 1) 
    Rollback
END ELSE BEGIN
    insert into dbo.FaildMessages select * from @Failed
    COMMIT TRAN
    SELECT Cast(SCOPE_IDENTITY() as int) 
END

2013 年 2 月编辑

@MartinSmith 提醒我们这个错误不想被微软修复 https://connect.microsoft.com/SQLServer/feedback/details/765809/odd-behaviour-of-scope-identity-with-insert-into.

“由 Microsoft 发布于 2013 年 2 月 27 日下午 2:18 你好，马丁，我们 调查了这个问题，发现改变行为并不是一个有效的方法 很容易做的事情。这基本上需要重新定义一些 当 INSERT 和 OUTPUT INTO 目标都具有标识列时的行为。 鉴于问题的性质和不常见的情况，我们有 决定不解决这个问题。 -- Umachandar，SQL 可编程性 团队”

2012 年 10 月编辑

这是由一个错误引起的：

测试错误：

引用 OUTPUT 子句 doc http://msdn.microsoft.com/en-us/library/ms177564.aspx:

@@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 返回标识值 仅由嵌套 DML 语句生成，而不是由嵌套 DML 语句生成 外部 INSERT 语句。

经过测试似乎scope_identity()仅在外部操作是在具有标识列的表中插入时才有效：

测试1：删除

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

insert into #t
values ('a'),('b'),('c');

delete #t
output deleted.a into #d;

select SCOPE_IDENTITY(), * from #d;

     a i 
---- - - 
null a 1 
null b 2 
null c 3 

Test 2: 插入带有标识的外表 https://data.stackexchange.com/stackoverflow/query/81574?opt.textResults=true

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

insert into #t
values  ('x'),('x'),('x');

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

select scope_identity(), * from #d;

  a i 
- - - 
2 a 1 
2 b 2 

测试 3：在没有标识的情况下插入外表

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

insert into #t
values  ('x'),('x'),('x');

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

select scope_identity(), * from #d;


     a i 
---- - - 
null a 1 
null b 2