SQL Server OUTPUT clause

Question

I got a little stuck on why I can't get a “new identification” of the inserted row with the statement below. SCOPE_IDENTITY() just returns 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) 

Any help would be most appreciated.

Now I am using a workaround like this.

 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

You can try using a table variable for your output clause, which allows you to explicitly insert into 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) 

Answer 2

EDITED FEB'2013

@MartinSmith warns us that this error does not need to be fixed by Microsoft .

Posted by Microsoft on 02.27.2013 at 14:18 Hi Martin, We investigated the problem and found that changing behavior is not a simple thing. It would basically require redefining some of when both INSERT and OUTPUT INTO have identification columns. Given the nature of the problem and unusual scenario, we have decided not to fix the problem. - Umachandar, SQL Programming Command "

EDITED OCT'2012

This is caused by an error:

Test Error:

Defining a DOPUT doc document :

@@ IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT identifiers are generated only by a nested DML statement, and not generated by an external INSERT statement.

After checking. It seems that scope_identity () only works if the external operation is an insert in a table with identification columns:

Test 1: Remove

 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; ai ---- - - null a 1 null b 2 null c 3 

Test 2: Insert into an external table with identifier

 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; ai - - - 2 a 1 2 b 2 

Test 3: Insert into an external table without identification

 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; ai ---- - - null a 1 null b 2