dwain.c (8/29/2012)
Yeah, so now what I haven't tested it with is:Since you can use both OUTPUT and OUTPUT INTO within the same INSERT, UPDATE, DELETE or MERGE, can you also then use this OUTPUT as composable DML to INSERT into another table.
So, one statement gives you:
INSERT into 3 tables
DELETE from 1 table, INSERT into 2
UPDATE into 1, INSERT into 2
MERGE into 1, INSERT into 2
?
MERGE can also delete at the same time too can't it?
:w00t::hehe::w00t::hehe:
OUTPUT INTO doesn't appear to be nestable:
USE tempdb
GO
DROP TABLE ABC
CREATE TABLE ABC (Id INT, Name VARCHAR(10), [Level] VARCHAR(20))
INSERT INTO ABC VALUES (1, 'PK','Already in table'), (2, 'SK','Already in table')
DROP TABLE ABC1
CREATE TABLE ABC1 (Id INT, Name VARCHAR(10), [Level] VARCHAR(20))
INSERT INTO ABC1 VALUES (1, 'KKKKK','Already in table'), (2, 'MMMMM','Already in table')
SELECT * FROM ABC
SELECT * FROM ABC1
-----------------------------------------------
INSERT ABC1 (Id, Name, [Level])
SELECT ID, Name, 'First insert'
FROM (
UPDATE A SET
A.Name = 'PPPPP', a.[Level] = 'Innermost update'
OUTPUT deleted.Id, inserted.Name, 'Output Insert' INTO ABC1
OUTPUT deleted.Id, inserted.Name
FROM ABC A
JOIN ABC1 B ON A.Id = B.Id
WHERE A.Id = 1
) d1
SELECT * FROM ABC
SELECT * FROM ABC1
Msg 10720, Level 15, State 1, Line 17
An OUTPUT INTO clause is not allowed in a nested INSERT, UPDATE, DELETE, or MERGE statement.
Careful with this stuff, you could disappear...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden