You are here:Home » tsql » OUTPUT clause with Insert, Update and Delete statements in SQL Server 2005

OUTPUT clause with Insert, Update and Delete statements in SQL Server 2005

Sometime we are using triggers to get old and new values or new values for the historical data to save in another table. So we can get inserted, updated and deleted data using triggers.

But one amazing feature "Output" clause. This clause we are mostly using with stored procedures to get output variables. Output clause also can be used to insert, update and delete statements. I have never used it before. I don't want to miss the chance to use it right now.

Here I want to more brief you with some of the examples and small demo. Creating database and tables.
-- Creating database
CREATE DATABASE OutputDB

GO

USE OutputDB

GO

-- Creating table
IF( Object_id('OutputTable') > 0 )
  DROP TABLE OutputTable

GO

CREATE TABLE OutputTable
  (
     Id    INT,
     Value VARCHAR(10)
  )

GO
Output clause with Insert statement,
-- Using Output clause with Insert statement.
INSERT INTO OutputTable
output      inserted.Id,
            inserted.Value
SELECT 1,
       '1-Inserted'

INSERT INTO OutputTable
output      inserted.Id,
            inserted.Value
SELECT 2,
       '2-Inserted'

GO

Output clause with Inserting records in another table,
-- Output clause with Insert records in another table
-- Creating table
IF( Object_id('InsertedTable') > 0 )
  DROP TABLE InsertedTable

GO

CREATE TABLE InsertedTable
  (
     Id    INT,
     Value VARCHAR(10)
  )

GO

INSERT INTO OutputTable
output      inserted.Id,
            inserted.Value
INTO InsertedTable
SELECT 3,
       '3-Inserted'

INSERT INTO OutputTable
output      inserted.Id,
            inserted.Value
INTO InsertedTable
SELECT 4,
       '4-Inserted'

GO

-- Let's see ther inserted values in InsertedTable
SELECT ID,
       value
FROM   OutputTable

GO

SELECT ID,
       value
FROM   InsertedTable

GO

Output clause with Update Statement,
-- Output clause with Update statement
-- Creating table
IF( Object_id('UpdatedTable') > 0 )
  DROP TABLE UpdatedTable

GO

CREATE TABLE UpdatedTable
  (
     Id    INT,
     Value VARCHAR(10)
  )

GO

-- Here we are insering new values in UpdatedTable table
UPDATE OutputTable
SET    value = '1-updated'
output inserted.Id,
       inserted.Value
INTO UpdatedTable
WHERE  Id = 1

-- Here we are inserting old values in UpdatedTable table
UPDATE OutputTable
SET    value = '2-updated'
output deleted.Id,
       deleted.Value
INTO UpdatedTable
WHERE  Id = 2

-- Let's chek the records in UpdatedTable
SELECT ID,
       Value
FROM   OutputTable

GO

SELECT ID,
       Value
FROM   UpdatedTable

GO

Output clause with Delete statement,
-- Output clause with Delete statement
-- Creating table
IF( Object_id('DeletedTable') > 0 )
  DROP TABLE DeletedTable

GO

CREATE TABLE DeletedTable
  (
     Id    INT,
     Value VARCHAR(10)
  )

GO

-- Here we are insering old values in DeletedTable table
-- which are GOing to be delete
DELETE FROM OutputTable
output deleted.Id,
       deleted.Value
INTO DeletedTable
WHERE  Id = 3

-- Here we are inserting old values in UpdatedTable table
DELETE FROM OutputTable
output deleted.Id,
       deleted.Value
INTO DeletedTable
WHERE  Id = 4

-- Let's chek the records in UpdatedTable
SELECT ID,
       Value
FROM   OutputTable

GO

SELECT ID,
       Value
FROM   DeletedTable

GO

Get output in different way!