Peter Larsson Blog

-- Setup TableA & TableB

CREATE TABLE          #TableA

                      (

                                 i INT

                      )

CREATE TABLE          #TableB

                      (

                                 i INT

                      )

CREATE TABLE          #TableC

                      (

                                 iOld INT,

                                 iNew INT

                      )

-- Check TableA and TableB

SELECT 'A' AS [Table], * FROM #TableA

UNION ALL

SELECT 'B' AS [Table], * FROM #TableB

-- Insert into TableA

INSERT     #TableA

OUTPUT     inserted.i

INTO       #TableB

SELECT     1 UNION ALL

SELECT     2 UNION ALL

SELECT     3

-- Check TableA and TableB

SELECT 'A' AS [Table], * FROM #TableA

UNION ALL

SELECT 'B' AS [Table], * FROM #TableB

-- Delete from TableA

DELETE     a

OUTPUT     10 * deleted.i + 49

INTO       #TableB

FROM       #TableA AS a

WHERE      i = 2

-- Check TableA and TableB

SELECT 'A' AS [Table], * FROM #TableA

UNION ALL

SELECT 'B' AS [Table], * FROM #TableB

-- Update TableB

UPDATE     b

SET        b.i = 1000 + b.i

OUTPUT     deleted.i AS [Old i value],

           inserted.i AS [New i value]

FROM       #TableB AS b

-- Check TableA and TableB

SELECT 'A' AS [Table], * FROM #TableA

UNION ALL

SELECT 'B' AS [Table], * FROM #TableB

-- Update TableB again

UPDATE     b

SET        b.i = b.i - 500

OUTPUT     deleted.i AS [Old i value],

           inserted.i AS [New i value]

INTO       #TableC

FROM       #TableB AS b

-- Check TableA and TableB

SELECT 'A' AS [Table], * FROM #TableA

UNION ALL

SELECT 'B' AS [Table], * FROM #TableB

-- Check TableC

SELECT 'C' AS [Table], * FROM #TableC

-- Clean up

DROP TABLE #TableA,

           #TableB,

           #TableC