Output Clause in SQL Server

Introduction

The output clause is very similar to a SELECT Query. We can use the OUTPUT clause in various scenarios.

We can perform the output clause in various statements. That is:

  1. Insert with Output.
  2. Delete with Output.
  3. Update with Output.
  4. Merge Statement With Output.

We will see each of them in the preceding in detail.

In a real scenario whenever we are inserting, deleting and updating large or bulk data to the table at the same time we need to return the information Inserted rows, Deleted rows and Updated rows that data back to client application.

With an output clause we can also get the data back with some little bit of effort. By using output it's easy to handle.

Insert with Output

We can use an output clause to return the information from Inserted rows in Insert Statement.

  1. CREATE DATABASE DEMOS  
  2. USE DEMOS  
  3.   
  4. --CREATE EMP TABLE  
  5. CREATE TABLE DBO.EMP  
  6. (  
  7.     ID INT IDENTITY(1,1) PRIMARY KEY,  
  8.     NAME VARCHAR(100) ,  
  9.     SALARY MONEY,  
  10.     DEPT INT   
  11. )  
  12.   
  13.   
  14. --INSERT SOME DATA TO EMP TABLE WITH OUTPUT CLAUSE   
  15. INSERT INTO DBO.EMP(NAME,SALARY,DEPT)  
  16. OUTPUT INSERTED.*  
  17. VALUES('RAKESH',24000,1),('NARESH',48000,1),('PRAVEEN',33000,2)  

 

Figure 1: Insert

When we run the preceding query we get the following output.

 

Figure 2: Insert Output

The following is the output with specified columns only (without *):

  1. --TRUNCATE ABOVE DATA  
  2. TRUNCATE TABLE DBO.EMP  
  3.   
  4. --INSERT SOME DATA TO EMP TABLE WITH OUTPUT CLAUSE (COLUMN_NAMES)  
  5. INSERT INTO DBO.EMP(NAME,SALARY,DEPT)  
  6. OUTPUT INSERTED.ID,INSERTED.NAME,INSERTED.DEPT  
  7. VALUES('RAKESH',24000,1),('NARESH',48000,1),('PRAVEEN',33000,2)  
  8. DROP TABLE  DBO.EMP  

 

Figure 3: Output with Specified Column

When we run the preceding query we get the following output.

 

Figure 4: Specified Column Output

The following is the output with a Column Alias:

  1. --TRUNCATE ABOVE DATA  
  2. TRUNCATE TABLE DBO.EMP  
  3.   
  4. --INSERT SOME DATA TO EMP TABLE WITH OUTPUT CLAUSE (WITH COLUMN ALIAS)  
  5. INSERT INTO DBO.EMP (NAME, SALARY, DEPT)  
  6. OUTPUT INSERTED.ID, INSERTED.FIRTSNAME AS [FULL_NAME], INSERTED.DEPT  
  7. VALUES ('RAKESH', 24000, 1), ('NARESH', 48000, 1), ('PRAVEEN', 33000, 2)  

 

Figure 5: Output with Column Alias

When we run the preceding query we can get the following output.

 

Figure 6: Column Alias Output

Delete with Output

We can use the output clause to return the information from the deleted rows in the Delete statement.

  1. --DELETE FROM EMP TABLE WHOSE DEPT IS -1 (WITH OUTPUT)  
  2. DELETE FROM DBO.EMP  
  3. OUTPUT DELETED.*  
  4. WHERE DEPT=1  

 

Figure 7: Delete

Update with Output

We can use the output clause to return the information from updated rows in the update statement.

In update with the output we need to see the following 2 options:

  1. Inserted: Means the rows of column values that are after the update.
  2. Deleted: Means the rows of column values that are before the update.
  1. --UPDATE FROM EMP TABLE (WITH OUTPUT)  
  2. UPDATE DBO.EMP SET SALARY=SALARY+10000  
  3. OUTPUT DELETED.SALARY [OLDSALARY], INSERTED.SALARY [NEWSALARY]   

 

Figure 8: Update

Merge Statement with Output

We can also use the Output clause with a Merge statement. When we are using the merge statement with the Output clause we need to identify which operation can be performed either insert, update or delete.

For this using $action function.

$action returns the :

If the row is inserted it returns INSERT.

If the row is updated it returns UPDATE.

If the row is deleted it returns DELETE.

  1. --MERGE STATEMENT WITH OUTPUT CLAUSE  
  2. MERGE INTO DBO.EMP AS TRG  
  3. USING (VALUES ('MADHU',25000,1),('RAKESH',30000,1)) AS SRC (NAME,SALARY,DEPT)  
  4. ON SRC.NAME=TRG.NAME  
  5. WHEN NOT MATCHED BY TARGET   
  6. THEN INSERT  VALUES(SRC.NAME,SRC.SALARY,SRC.DEPT)  
  7. WHEN MATCHED AND (TRG.SALARY<>SRC.SALARY OR TRG.DEPT<>SRC.DEPT)  
  8. THEN UPDATE SET TRG.SALARY=SRC.SALARY,TRG.DEPT=SRC.DEPT  
  9. WHEN NOT MATCHED BY SOURCE  
  10. THEN DELETE  
  11. OUTPUT $ACTION AS [ACTION],COALESCE(INSERTED.NAME,DELETED.NAMEAS [NAME];  

 

Figure 9: Merge Statement

When we run the preceding query we can get the following output.

 

Figure 10: Merge Statement Output

We can also use two output clauses whenever an operation is performed.

One for INTO.

Another for Normal.

 

  1. CREATE TABLE EMP_BACKUP  
  2. (NAME VARCHAR(100),DEPT INT)  
  3.   
  4. INSERT INTO DBO.EMP(NAME,SALARY,DEPT)  
  5. OUTPUT INSERTED.NAME AS [FULL_NAME],INSERTED.DEPT INTO EMP_BACKUP(NAME,DEPT)  
  6. OUTPUT INSERTED.*  
  7. VALUES('RAKESH',24000,1),('NARESH',48000,1),('PRAVEEN',33000,2)  

 

Note: When we use INTO with table name the table must follow 2 conditions.

  1. The table doesn't contain a foreign key column.
  2. The table doesn't participate in triggers.