SQL Server OUTPUT clause

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提醒我们，这个错误不希望被微软修复。

"由微软发布于2013年2月27日下午2:18你好，马丁，我们调查了这个问题，发现改变行为不是简单的事情。这基本上需要重新定义当INSERT&OUTPUT INTO目标具有标识列。鉴于问题的性质&不常见的情况，我们决定不解决这个问题。——Umachandar，SQL可编程性团队；

2012年10月编辑

这是由一个错误引起的：

测试错误：

报价输出条款文档：

@@IDENTITY、SCOPE_IDENTITY和IDENT_CURRENT返回标识值仅由嵌套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 

测试2：插入具有标识的外表

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 

Answer 2

您可以尝试为output子句使用一个表变量，从而允许显式插入FaildMessages:

declare     @WorkRequestQueueID int
declare @LastException nvarchar(MAX)
set @WorkRequestQueueID = 1
set @LastException = 'test'
set nocount off
-- Declare a table variable to capture output
DECLARE @output TABLE (
MessageEnvelope VARCHAR(50),    -- Guessing at datatypes
Attempts INT,                   -- Guessing at datatypes
WorkItemReceived_UTC DATETIME   -- Guessing at datatypes
)
-- Run the deletion with output
DELETE dbo.WorkRequestQueue
OUTPUT
DELETED.MessageEnvelope,
DELETED.Attempts,
DELETED.WorkItemReceived_UTC
-- Use the table var
INTO @output
FROM dbo.WorkRequestQueue
WHERE
WorkRequestQueue.ID = @WorkRequestQueueID
-- Explicitly insert
INSERT
INTO dbo.FaildMessages
SELECT
MessageEnvelope,
Attempts,
@LastException,
GetUtcdate(), -- WorkItemPoisened datetime
WorkItemReceived_UTC
FROM @output

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

SELECT Cast(SCOPE_IDENTITY() as int)