Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Basic OUTPUT statement
Published: Jun 24, 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


Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.