Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

New SQL Server 2005 OUTPUT operator

  Let’s play with the new OUTPUT operator!

-- 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

 

 

 

Print | posted on Wednesday, October 03, 2007 6:44 PM | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET