Using Output

The OUTPUT command can be a very useful tool to use at times. The following scripts show a couple of ways it can be used. The code from these scripts can be found here on my shared dropbox.

First off we create a couple of tables for the examples used.

IF EXISTS ( SELECT name from sysobjects WHERE Name ='DummyData') DROP TABLE DummyData
CREATE TABLE DummyData
(
       ID INT identity(1,1),
       TableName     sysname,
       ColumnsName sysname,
       Col_id INT,
       CreateDate    DATETIME,
)
 
--Create table to hold some dummy data
IF EXISTS ( SELECT name from sysobjects WHERE Name ='NewDummyData') DROP TABLE NewDummyData
CREATE TABLE NewDummyData
(
       ID INT identity(1,1),
       TableColumnName      sysname,
       Col_id INT
)

 

Then populate 10K record using a clever trick using a CROSS JOIN and system tables
INSERT INTO DummyData
SELECT TOP (10000)
       o.name,
       c.name,
       c.column_id,
       o.create_date
FROM
       sys.columns c
CROSS JOIN
       sys.objects o
WHERE
       o.Type ='U'  

 

Running the following code will delete the number of rows specified in the variable setting so 10 in this case.

DECLARE @DeletionSize INT =10
DELETE TOP (@DeletionSize) FROM DummyData
OUTPUT Deleted.*

 

It will also display the record that have been deleted using OUTPUT deleted.* as shown below.

Output1

 

You can use the following to reduce the size of delete statements on the transaction log by looping through a table, obviously TRUNCATE is preferred but you cant always use that if Foreign Keys exist or you don’t have permissions

DECLARE @DeletionSize INT =1000
 
WHILE 1=1
BEGIN
       DELETE TOP (@DeletionSize) FROM DummyData
       IF @@rowcount =0 BREAK
END
 

 

You can also manipulate the deleted statement from OUTPUT. Repopulating the table with the code in the previous examples

DECLARE @DeletionSize INT =10
 
DELETE TOP (@DeletionSize) FROM DummyData
OUTPUT Deleted.TableName+'.'+deleted.ColumnsName AS TableColumn,deleted.Col_id

 

The following image shows the results, the Table and column have been concatenated together and col_id, other columns have been ignored

Output2

This can start to be come useful when you can output the deleted records into another table. This can be used for Archiving data to Archive tables , Auditing purposes or moving data from one database to another on the same server. Its a single transaction rather then a copy of the data and then a delete.

DECLARE @DeletionSize INT =10
 
DELETE TOP (@DeletionSize) FROM DummyData
OUTPUT Deleted.TableName+'.'+deleted.ColumnsName,deleted.Col_id INTO NewDummyData(TableColumnName,Col_id)

 

So if we wanted to move a large table we can merge the technique that was used earlier and loop through the table.

DECLARE @DeletionSize INT =10
 
WHILE 1=1
BEGIN
       DELETE TOP (@DeletionSize) FROM DummyData
       OUTPUT Deleted.TableName+'.'+deleted.ColumnsName,deleted.Col_id INTO NewDummyData(TableColumnName,Col_id)
       IF @@rowcount =0 BREAK
END

 

 

You can use a table variable to stored the output of the data. For example the next set of code stores the record id for each iteration and returns the last record deleted. You can manipulate this how ever you want.

--Truncate is used to reset the seed value
TRUNCATE TABLE DummyData
GO
INSERT INTO DummyData
SELECT TOP (100)
       o.name,
       c.name,
       c.column_id,
       o.create_date
FROM
       sys.columns c
CROSS JOIN
       sys.objects o
WHERE
       o.Type ='U'  
 
 
GO
 
 
DECLARE @DeletionSize INT =12
DECLARE    @MaxRecords TABLE (recordid int)
 
 
BEGIN
       DELETE TOP (@DeletionSize) FROM DummyData
       OUTPUT Deleted.ID INTO @MaxRecords
       WHERE ID In (SELECT TOP (@DeletionSize) ID FROM DummyData ORDER BY ID)
      
       SELECT MAX(recordid) AS RecordID FROM @MaxRecords
END
 
 GO 2

 

I also used a useful trick highlighted in the red circle below, adding a number after the GO batch separator will run the code from the previous GO statement that many times. So you can see this is ran twice and output the max record for each run.
Output3

The OUTPUT statement can also be used with UPDATE statement as well.

IF EXISTS ( SELECT name from sysobjects WHERE Name ='DummyAccounts') DROP TABLE DummyAccounts
CREATE TABLE DummyAccounts
(
       ID INT identity(1,1),
       AccountName   sysname,
       Balance INT
)
 
GO
 
INSERT INTO DummyAccounts(AccountName,Balance)
SELECT 'Martin Croft',1
GO
 
SELECT * FROM DummyAccounts
 
 
 
--Used SQL 2008 notation += increase the balance by er a million
 
UPDATE DummyAccounts
SET Balance+=1000000
OUTPUT deleted.AccountName,deleted.Balance AS OldBalance ,Inserted.Balance AS NewBalance
WHERE AccountName ='Martin Croft'
 

 

 

As shown below , you can used Deleted.Balance and Inserted.Balance to show the before and after values as records change in the table. Perfect for Auditing changes, and my bank balance is much higher now! 

image

You can get very clever with OUTPUT is can also be used within MERGE statements, which I have blogged about previously here

Advertisements
This entry was posted in Tips & Tricks, TSQL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s