sql-server - SQL Server 输出子句

Question

我有点困惑为什么我似乎无法使用下面的语句获得插入行的“新身份”。 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

Answer 1

2013 年 2 月编辑

@MartinSmith 提醒我们此错误 don't want be fixed by Microsoft .

"Posted by Microsoft on 2/27/2013 at 2:18 PM Hello Martin, Weinvestigated the issue and found that changing the behavior is not aneasy thing to do. It would basically require redefining some of thebehavior when both INSERT & OUTPUT INTO target has identity columns.Given the nature of the problem & the uncommon scenario, we havedecided not to fix the issue. -- Umachandar, SQL ProgrammabilityTeam"

2012 年 10 月编辑

这是由错误引起的:

测试错误:

Quoting OUTPUT Clause doc :

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT return identity valuesgenerated only by the nested DML statement, and not those generated bythe outer INSERT statement.

经过测试，似乎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 

测试 2:Inserting in outer table with identity

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