Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

How to use OUTPUT for Insert, Update and Delete in SQL

- 19.09.23 - ErcanOPAK

The OUTPUT clause was introduced in SQL Server 2005. The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE, or DELETE statement. It even supports a MERGE statement, which was introduced in SQL Server 2008 version.

The OUTPUT clause has access to two temporary or in-memory SQL tables, INSERTED and DELETED. These tables are populated when an INSERT/UPDATE/DELETE operation is done on a table. As a result, the OUTPUT clause can provide us the affected records by referencing these tables. So let’s see how to do this. But before we proceed with the discussion, we need to discuss key points related to these tables:

  • When a new record is inserted into a table, say Employee, a new record is added to the INSERTED table.
  • When a record is updated in a table, a new record with the old values is added to the DELETED table and a new record, with the new values is added to the INSERTED table.
  • When a record is deleted from a table, a new record, with the values of the record being deleted, is added to the DELETED table.

For our discussion, we will use two tables named Employee and Department. So let’s discuss the cases one by one.

  1. OUTPUT with the INSERT statement: Simply provide the name of the columns you would like to be fetched, using the Inserted.Column_Name syntax, just after the INSERT INTO and before the actual values to be inserted. See the query below:

    OUTPUT with the INSERT statement

  2. OUTPUT with DELETE statement: The concept remains the same, except the Inserted.Column_Name is replaced with DELETED.Column_Name, since we are looking for the records that are affected by the DELETE statement. See the query below:

    OUTPUT with DELETE statement

  3. OUTPUT with UPDATE statement: For any UPDATE operation, the records to be updated are first deleted and then inserted into the temporary tables. So we can get both the old and new record values. Let’s update the records and see the results:

    OUTPUT with UPDATE statement

    Since we can see the results, we have the old value as the Deleted one and the new value as the Inserted one.

  4. OUTPUT with JOIN UPDATE/DELETE: Apart from these operations, we can also use OUTPUT to get the records affected when we are using a JOIN statement to UPDATE/DELETE bulk records. For example, we create a table Department and insert sample data into it. Also,  some related records are inserted into the Employee table. So we have the setup below:

    OUTPUT with JOIN UPDATE or DELETETo test this case, we will update the records to the Employee table, for those who belong to the “HR” department, in other words records with the Ids 1, 2, 18 and 19. So let’s write a query with INNER JOIN, to update the results use the OUTPUT keyword to see the affected records. So we write our query as:

    affected records

Now let’s see how to store these values in temporary tables or table variables. For this, we will add some more records to the table.

Store data into Temporary table: We simply create a new temporary table and insert the records into it using the INTO keyword. See the UPDATE query below, that stores the data into a temporary table:

Store data into Temporary table

Store data into Table variable: No changes except that the temporary table is replaced with a table variable now. We will now use it with the DELETE statement. So our query changes to the following:

Store data into Table variable

This keyword becomes a very powerful option when we have Stored Procedures, where we are manipulating the data and need to know what and how the records are being affected. So this was about the use of the OUTPUT keyword.

Thank you so much Jasminder Singh from C#Corner for this amazing article. This was the best one so far.

Related posts:

SQL Server: Fix a Temp Table Causing a Plan Recompile Every Call

SQL Server: Fix a Temp Table Causing a Plan Recompile Every Call

SQL Server: Fix a Table-Valued Parameter That Silently Passes Zero Rows Because the Same DataTable I...

SQL Server: Fix a Table-Valued Parameter That Silently Passes Zero Rows Because the Same DataTable I...

SQL Server: Fix a CHECK Constraint That Silently Stops Enforcing New Violations After a Bulk Load Le...

SQL Server: Fix a CHECK Constraint That Silently Stops Enforcing New Violations After a Bulk Load Le...

Views: 114

Post navigation

How to get the first and last date of the current year in SQL
How to convert JSON to XML or XML to JSON in C#

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (661)
  • Add Constraint to SQL Table to ensure email contains @ (619)
  • Average of all values in a column that are not zero in SQL (576)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (553)
  • Find numbers with more than two decimal places in SQL (481)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store
  • WordPress: Fix a Navigation Block Losing Submenu Items After Sync
  • Visual Studio: Fix CodeLens Reference Counts Stuck at Stale Numbers
  • C#: Fix a UTF-8 String Literal Behaving Differently in a Switch
  • Photoshop: Fix a Liquify Mesh Snapping Back on a Smart Object
  • The AI Prompt That Turns a Crash Dump Into a Short Repro Script

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com