Loading, please wait...

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.
  1. When to use the output in SQL.
  2. Output usage with DML statements.
    1. INSERT
    2. DELETE
    3. UPDATE
    4. 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. 
  1. DELETE table1    
  2. OUTPUT DELETED.*     
  3. WHERE ID = 6;  
Result:- Delete with output query result shows in the below image displaying deleted data in the result window, as in Insert statement we used the table variable "@dataForHR" here we didn't use any table variable still the data selected in the OUTPUT statement will be displayed to us. This can help us to take a backup of the result, whereas table variable usage can help in executing any logic while we are executing it.

 
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.

MCQ

Related Article