Peter Larsson Blog

Patron Saint of Lost Yaks

New SQL Server 2005 OUTPUT operator

Let’s play with the new OUTPUT operator!/o:p

/o:p

– Setup TableA & TableB/o:p

CREATE TABLE          #TableA/o:p

                      (/o:p

                                 i INT/o:p

                      )/o:p

 /o:p

CREATE TABLE          #TableB/o:p

                      (/o:p

                                 i INT/o:p

                      )/o:p

 /o:p

CREATE TABLE          #TableC/o:p

                      (/o:p

                                 iOld INT,/o:p

                                 iNew INT/o:p

                      )/o:p

 /o:p

– Check TableA and TableB/o:p

SELECT 'A' AS [Table], FROM #TableA/o:p

UNION ALL/o:p

SELECT 'B' AS [Table], FROM #TableB/o:p

 /o:p

– Insert into TableA/o:p

INSERT     #TableA/o:p

OUTPUT     inserted.i/o:p

INTO       #TableB/o:p

SELECT     1 UNION ALL/o:p

SELECT     2 UNION ALL/o:p

SELECT     3/o:p

 /o:p

– Check TableA and TableB/o:p

SELECT 'A' AS [Table], FROM #TableA/o:p

UNION ALL/o:p

SELECT 'B' AS [Table], FROM #TableB/o:p

 /o:p

– Delete from TableA/o:p

DELETE     a/o:p

OUTPUT     10 deleted.i + 49/o:p

INTO       #TableB/o:p

FROM       #TableA/st1:city AS/st1:state/st1:place a/o:p

WHERE      i = 2/o:p

 /o:p

– Check TableA and TableB/o:p

SELECT 'A' AS [Table], FROM #TableA/o:p

UNION ALL/o:p

SELECT 'B' AS [Table], FROM #TableB/o:p

 /o:p

– Update TableB/o:p

UPDATE     b/o:p

SET        b.i = 1000 + b.i/o:p

OUTPUT     deleted.i AS [Old i value],/o:p

           inserted.i AS [New i value]/o:p

FROM       #TableB/st1:city AS/st1:state/st1:place b/o:p

 /o:p

– Check TableA and TableB/o:p

SELECT 'A' AS [Table], FROM #TableA/o:p

UNION ALL/o:p

SELECT 'B' AS [Table], FROM #TableB/o:p

 /o:p

– Update TableB again/o:p

UPDATE     b/o:p

SET        b.i = b.i - 500/o:p

OUTPUT     deleted.i AS [Old i value],/o:p

           inserted.i AS [New i value]/o:p

INTO       #TableC/o:p

FROM       #TableB/st1:city AS/st1:state/st1:place b/o:p

 /o:p

– Check TableA and TableB/o:p

SELECT 'A' AS [Table], FROM #TableA/o:p

UNION ALL/o:p

SELECT 'B' AS [Table], FROM #TableB/o:p

 /o:p

– Check TableC/o:p

SELECT 'C' AS [Table], FROM #TableC/o:p

 /o:p

– Clean up/o:p

DROP TABLE #TableA,/o:p

           #TableB,/o:p

           #TableC/o:p

 /o:p

 /o:p

 

/o:p