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.
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
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.
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!
You can get very clever with OUTPUT is can also be used within MERGE statements, which I have blogged about previously here
