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 use with stored procedures to get output variables. Output clause also can be used with 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 check 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!