USE tempdb
GO
DROP TABLE ABC
CREATE TABLE ABC (Id INT, Name VARCHAR(10))
INSERT INTO ABC VALUES (1, 'PK'), (2, 'SK')

DROP TABLE ABC1
CREATE TABLE ABC1 (Id INT, Name VARCHAR(10))
INSERT INTO ABC1 VALUES (1, 'KKKKK'), (2, 'MMMMM')

SELECT * FROM ABC
SELECT * FROM ABC1

INSERT ABC1 (Id, Name)
SELECT *
FROM (
	UPDATE A SET 
		A.Name = 'PPPPP'
	OUTPUT deleted.Id, inserted.Name	
	FROM ABC A 
	JOIN ABC1 B ON A.Id = B.Id
	WHERE A.Id = 1
) d 

SELECT * FROM ABC
SELECT * FROM ABC1

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?

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.

CREATE TABLE ABC (Id INT NOT NULL , Name VARCHAR(10), Tab CHAR(1) NOT NULL DEFAULT('A') CHECK (Tab='A'), PRIMARY KEY(Id,Tab))
INSERT INTO ABC(Id,Name) VALUES (1, 'PK'), (2, 'SK')

CREATE TABLE ABC1 (Id INT NOT NULL, Name VARCHAR(10), Tab CHAR(1) NOT NULL DEFAULT('B') CHECK (Tab='B'), PRIMARY KEY(Id,Tab) )
INSERT INTO ABC1(Id,Name) VALUES (1, 'KKKKK'), (2, 'MMMMM')

GO

CREATE VIEW myView AS 
SELECT Tab,Id,Name
FROM ABC
UNION ALL
SELECT Tab,Id,Name
FROM ABC1

GO

SELECT Id,Name FROM ABC
SELECT Id,Name FROM ABC1

UPDATE myView
SET Name = 'PPPPP'
WHERE Id=1
       
SELECT Id,Name FROM ABC
SELECT Id,Name FROM ABC1