In SQL SERVER an OUTPUT keyword/ Clause is a very good feature for auditing purpose.
It performs with Data manipulation language (DML) statements and returns a copy of the data that you have inserted, updated or deleted from table.
You can also store the return data by OUTPUT clause into temp table or permanent table.
Types of OUTPUT Clause:
1-OUTPUT : It returns the copy of data as in result set.
Syntax: INSERT INTO tblname OUTPUT INSERTED.Column 1, INSERTED.Column 2.... INSERTED.Columns n VALUES(Value_1, Value_2, Value n)
2-OUTPUT INTO : It is used, when you want to copy data into table or a table variable.
Syntax: INSERT INTO tblname OUTPUT INSERTED.Column 1, INSERTED.Column 2.... INSERTED.Columns n INTO #temptablename VALUES(Value_1, Value_2, Value n)
OUTPUT Clause support two types of columns prefix:
- INSERTED: Is a column prefix that specifies the value added by the insert or update statement.
- DELETED: Is a column prefix that specifies the value deleted by the update or delete statement.
Let’s start with an example:
Step 1: Create table and insert few records
create table employee ( id int IDENTITY(1, 1) primary key, firstname varchar(10) not null, lastname varchar(10) not null, country varchar(10) not null ) insert into employee(firstname, lastname,Country) values('King','Lee','China')
Step 2: Now insert record with OUTPUT clause
insert into employee OUTPUT inserted.firstname, inserted.lastname, inserted.Country values('Lui','OOi','Japan')
When you insert record into table using OUTPUT clause with column prefix INSERTED, it returns the inserted data as in result set.
Step 3: Update records with OUTPUT clause
Here we using Deleted column prefix before firstname & lastname, because we want to add old deleted changes into table output and Inserted prefix before country name means there is no change in country name.
Update employee Set firstname='Binoy', lastname='Ui' OUTPUT deleted.firstname,deleted.lastname, inserted.Country where id=1
Step 4: Delete records with OUTPUT Clause
Delete employee OUTPUT deleted.id, deleted.firstname, deleted.lastname, deleted.Country where id=1
How to Insert Deleted rows into Temp table using OUTPUT Clause?
Step 1: Create one temp table
Create table #employee (id int IDENTITY(1, 1) primary key, firstname varchar(100), lastname varchar(100), Country varchar(100))
Step 2: Now delete particular record from employee table, here we used OUTPUT clause with INTO.
Delete employee OUTPUT deleted.id, deleted.firstname, deleted.lastname, deleted.Country INTO #employee where id=2
Hope you enjoyed the post, Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.
Recommended SQL Server Post:
Dirty Read Concurrency Problems
SQL CAST() Vs TRY_CAST() function
SQL Server Configuration Functions
Identity functions in SQL Server