Предложение SQL Server OUTPUT

Question

Я немного застрял в том, почему я не могу получить "новую идентичность" вставленной строки с помощью приведенного ниже утверждения. SCOPE_IDENTITY() просто возвращает ноль.

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

Вы можете попробовать использовать переменную таблицы для вашего 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) 

Answer 2

РЕДАКТИРОВАНИЕ ФЕВРАЛЯ 2013 ГОДА

@MartinSmith предупреждает нас, что Microsoft не хочет исправлять эту ошибку.

"Опубликовано Microsoft 27.02.2013 в 14:18 Здравствуйте, Мартин! Мы исследовали проблему и обнаружили, что изменение поведения - непростая задача. По сути, для этого потребуется переопределить некоторые из них, когда и INSERT, и OUTPUT У цели INTO есть столбцы идентификаторов. Учитывая природу проблемы и необычный сценарий, мы решили не устранять проблему. - Умачандар, команда программирования SQL "

РЕДАКТИРОВАННОЕ ОКТЯБРЬ 2012

Это вызвано ошибкой:

Ошибка тестирования:

Цитировать выходной пункт документа:

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