What are Output clause in SQL ?
Sep 06, 2020
Audit using SQL,
deleted in SQL,
how to use output in SQL,
inserted in SQL,
Magic Tables in SQL,
merge in SQL,
Output in SQL,
output use with delete,
output use with delete in SQL,
output use with insert,
output use with insert in SQL,
output use with merge,
output use with merge in SQL,
output use with update,
output use with update in SQL,
SQL output,
10476 Views
Usage of output clause in SQL.
Output Clause in SQL
SQL Server lets you add an OUTPUT clause to INSERT, UPDATE, or DELETE statements. SQL Server also lets you add the clause to MERGE statements. In this article, I demonstrate the scenarios when can it be used in realtime.
Following items covered in this article of "Output" in SQL.
- When to use the output in SQL.
- Output usage with DML statements.
- INSERT
- DELETE
- UPDATE
- MERGE
When to use the output in SQL.
It mostly used in the store procedures,
Scenario 1: If you want to develop an audit feature where you won't know what the previous data was and with what it has been changed with.
Scenario 2: If you are writing the data migration script where you will be having a complex logic want to retain the updated/inserted record to perform another set of operations with that data in the same-store procedure.
Output usage with DML statements.
INSERT Statement.
--Table with 4 columns
SELECT * FROM table1
--Ouput with insert statement
DECLARE @dataForHR table(Id int,Salary decimal)
INSERT INTO table1(id,Employee,Designation,Basicpay)
OUTPUT inserted.Id,inserted.Basicpay INTO @dataForHR
VALUES (6, 'MKP', 'HR', 55000)
,(5, 'Nuoh', 'Dev', 75000)
SELECT * FROM @dataForHR Result:- Insert with output query result shows in the below image having data in table variable called "@dataForHR", it shows the newly inserted data into the table.
Delete Statement.
- DELETE table1
- OUTPUT DELETED.*
- WHERE ID = 6;
Update Statement.
DECLARE @dataForHR table(Id int,NewSalary decimal,OldSalary decimal)
UPDATE table1
SET Basicpay = 50000.00
OUTPUT inserted.id, inserted.BasicPay, deleted.BasicPay INTO @dataForHR
WHERE id in (5,3) Result:- Update with output query result shows in the below image displaying new value and old value for BasicPay.
As you guys notice that we are using the two keywords called "inserted" and "deleted" along with the Output statement. These two are the predefined tables in the SQL database and these tablets called "Magic tables". The output shows by the Update query result is the perfect example for the usage of Audit Scenario, in the example, we use the table variable instead you can use the actual tables meant for Audit purpose.
Merge Statement.
--Ouput with merge statement
DECLARE @inputData table(id int,Employee varchar(25),Designation varchar(50),Basicpay decimal)
Insert into @inputData values (6, 'MKP', 'HR', 55000)
,(5, 'Nuoh', 'Dev', 75000)
,(7, 'Nabhiya', 'Dev', 45000.00)
,(8, 'Nuz', 'Dev', 65000.00)
DECLARE @outputData table(Id int,NewSalary decimal,OldSalary decimal,Action varchar(20))
MERGE table1 AS target
USING @inputData AS source
ON (target.id = source.id)
WHEN MATCHED THEN
UPDATE SET BasicPay = source.BasicPay
WHEN NOT MATCHED THEN
INSERT (id,Employee,Designation,BasicPay)
VALUES (source.id,source.Employee,source.Designation, source.BasicPay)
OUTPUT inserted.id,inserted.BasicPay,deleted.BasicPay,$action INTO @outputData;
Select * from @outputData Result:- Merge with output query result shows in the below image displaying new value and old value for BasicPay, along with the action says Inserted and Updated. We can even have a delete statement with a merge when not matched with source and target.
Merge example is best suitable for Data migration and the Bulk upload of data using any dataset. In our example, we used the @inputData table variable instead it can be any sort of dataset xlsx file data, CSV file data, etc.
Summary
As shown in the above examples scenario where you can use the OUTPUT clause in our realtime scenarios.
