Basic OUTPUT statement
Published: 3rd May 2013
Within SQL Server there is a very useful clause that you can combine with all statements (INSERT, UPDATE, DELETE) in order to provide further information or details as to what’s happening… that is the OUTPUT statement.

Effectively what it does is, as implied, output the changes you have made by using the “inserted” and “deleted” tables that triggers can also access. These are “before” and “after” style tables allowing you to see what was inserted, deleted, and updated. I’m finding this quite hard to explain so I think this is best shown in a series of examples:

INSERT



Let’s create a very basic name table (using table variables… if you don’t know what these are, please read my article here) with an identity column, first name, and last name.

If we insert a row into this table we can access the “inserted” table to give us extra information as to what’s just been entered including the identity value and also add additional columns such as a timestamp. You can therefore see how this would be ideal to provide some logging on your table:

declare @name table
(
  
id int identity(1, 1),
  
firstName varchar(100),
  
lastName varchar(100)
)
insert into @name
output inserted.id, inserted.firstName, inserted.lastName, CURRENT_TIMESTAMP
select 'Homer', 'Simpson'

insert into @name
output inserted.id, inserted.firstName, inserted.lastName, CURRENT_TIMESTAMP
select 'Marge', 'Simpson'

select *
from @name


DELETE

This works in very much the same way as the insert, but instead of using the inserted table it accesses one called “deleted”. In the example below I have also included the username of the person making the delete…

declare @name table
(
  
id int identity(1, 1),
  
firstName varchar(100),
  
lastName varchar(100)
)
insert into @name select 'Homer', 'Simpson'
insert into @name select 'Marge', 'Simpson'

delete from @name
output deleted.id, deleted.firstName, deleted.lastName, suser_sname() whoDeleted
where firstName = 'Homer'

select *
from @name


UPDATE

This command is a combination of both an insert and delete. You are effectively deleting the old data and inserting the new data. As such, using an update will give you access to both the inserted and deleted tables. This means you can pull out a lot more information than you could before…

declare @name table
(
  
id int identity(1, 1),
  
firstName varchar(100),
  
lastName varchar(100)
)
insert into @name select 'Homer', 'Simpson'
insert into @name select 'Marge', 'Simpson'

update @name
set firstName = 'Peter', lastName = 'Griffin'
output inserted.id, deleted.firstName as oldFirstName, deleted.lastName as oldLastName,
      
inserted.firstName as newFirstName, inserted.lastName as newLastName,
      
current_timestamp as updateTime, suser_sname() userName
where id = 1

select *
from @name


OUTPUT INTO

Obviously this data isn’t much use if you only see it as an output to a query, for any decent tracking data you would need this data to be stored. SQL accommodates this by allowing you to OUTPUT INTO a table…

declare @output table
(
  
id int,
  
oldFirstName varchar(100),
  
oldLastName varchar(100),
  
newFirstName varchar(100),
  
newLastName varchar(100),
  
updateTime datetime,
  
userName varchar(100)
)
declare @name table
(
  
id int identity(1, 1),
  
firstName varchar(100),
  
lastName varchar(100)
)
insert into @name select 'Homer', 'Simpson'
insert into @name select 'Marge', 'Simpson'

update @name
set firstName = 'Peter', lastName = 'Griffin'
output inserted.id, deleted.firstName as oldFirstName, deleted.lastName as oldLastName,
      
inserted.firstName as newFirstName, inserted.lastName as newLastName,
      
current_timestamp as updateTime, suser_sname() userName
into @output
where id = 1

select *
from @name

select *
from @output


SUMMARY

Output is a very powerful tool that you can use to verify the data that’s going in and out of your tables. Change control features do exist in SQL Server, but only at the Enterprise level and therefore many will never come across these therefore, in their absence, OUTPUT can be utilised as a very good change control device for your key tables. A basic example of this can be as follows…

declare @output table
(
  
id int,
  
oldFirstName varchar(100),
  
oldLastName varchar(100),
  
newFirstName varchar(100),
  
newLastName varchar(100),
  
updateTime datetime,
  
userName varchar(100),
  
changeType varchar(10)
)
declare @name table
(
  
id int identity(1, 1),
  
firstName varchar(100),
  
lastName varchar(100)
)
insert into @name
output inserted.id, null, null, inserted.firstName, inserted.lastName,
      
current_timestamp as updateTime, suser_sname() as userName, 'Insert'
into @output
select 'Homer', 'Simpson'

insert into @name
output inserted.id, null, null, inserted.firstName, inserted.lastName,
      
current_timestamp as updateTime, suser_sname() as userName, 'Insert'
into @output
select 'Marge', 'Simpson'

update @name
set firstName = 'Peter', lastName = 'Griffin'
output inserted.id, deleted.firstName as oldFirstName, deleted.lastName as oldLastName,
      
inserted.firstName as newFirstName, inserted.lastName as newLastName,
      
current_timestamp as updateTime, suser_sname() as userName, 'Update'
into @output
where id = 1

delete from @name
output deleted.id, deleted.firstName, deleted.lastName, null, null,
      
current_timestamp as updateTime, suser_sname() as userName, 'Delete'
into @output
where id = 2

select *
from @name

select *
from @output


Comments:
NB: Comments will only appear once they have been moderated.