Скопируйте строки таблицы, используя OUTPUT INTO в SQL Server 2005

Question

У меня есть таблица, из которой мне нужно скопировать записи обратно в себя. Как часть этого, я хочу захватить новые строки, используя предложение OUTPUT, в табличную переменную, чтобы я мог выполнять другие операции над строками также в том же процессе. Я хочу, чтобы каждая строка содержала свой новый ключ и ключ, с которого он был скопирован. Вот надуманный пример:

INSERT 
    MyTable (myText1, myText2) -- myId is an IDENTITY column
OUTPUT  
    Inserted.myId, 
    Inserted.myText1, 
    Inserted.myText2 
INTO
    -- How do I get previousId into this table variable AND the newly inserted ID? 
    @MyTable    
SELECT 
    -- MyTable.myId AS previousId,
    MyTable.myText1,
    MyTable.myText2
FROM
    MyTable
WHERE
    ...

SQL Server лает, если число столбцов в INSERT не совпадает с количеством столбцов из инструкции SELECT. Из-за этого я вижу, как это может работать, если я добавлю столбец в MyTable, но это не вариант. Ранее это было реализовано с помощью курсора, который вызывает узкое место в производительности - я намеренно пытаюсь избежать этого.

Как мне скопировать эти записи, сохранив ключ скопированной строки таким образом, чтобы достичь максимально возможной производительности?

Answer 1

Я немного неясен относительно контекста - это в триггере AFTER INSERT.

Во всяком случае, я не вижу способа сделать это за один звонок. Предложение OUTPUT позволит вам возвращать только те строки, которые вы вставили. Я бы порекомендовал следующее:

DECLARE @MyTable (
    myID INT, 
    previousID INT, 
    myText1 VARCHAR(20), 
    myText2 VARCHAR(20)
)

INSERT @MyTable (previousID, myText1, myText2) 
SELECT myID, myText1, myText2 FROM inserted

INSERT MyTable (myText1, myText2) 
SELECT myText1, myText2 FROM inserted

-- @@IDENTITY now points to the last identity value inserted, so...
UPDATE m SET myID = i.newID
FROM @myTable m, (SELECT @@IDENTITY - ROW_NUMBER() OVER(ORDER BY myID DESC) + 1 AS newID, myID FROM inserted) i
WHERE m.previousID = i.myID

...

Конечно, вы не будете помещать это в триггер AFTER INSERT, потому что это даст вам рекурсивный вызов, но вы можете сделать это в триггере INSTEAD OF INSERT. Я могу ошибаться в рекурсивной проблеме; Я всегда избегаю рекурсивного вызова, поэтому я так и не узнал. Однако использование @@IDENTITY и ROW_NUMBER() - это трюк, который я использовал несколько раз в прошлом, чтобы сделать что-то подобное.