SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE

SQL Server 2005 has new OUTPUT clause, which is quite useful. OUTPUT clause has accesses to inserted and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

OUTPUT clause can generate table variable, a permanent table, or temporary table. Even though, @@Identity will still work in SQL Server 2005, however I find OUTPUT clause very easy and powerful to use. Let us understand OUTPUT clause using example.
————————————————————————————————————————
—-Example 1 : OUTPUT clause into Table with INSERT statement
————————————————————————————————————————
USE AdventureWorks;
GO
--------Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table as well use OUTPUT clause to insert
----values in the temp table.
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (2,'SecondVal')
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO

ResultSet 1:
ID TextVal
——————— ————————
1 FirstVal
2 SecondVal

ID TextVal
——————— ———————
1 FirstVal
2 SecondVal

————————————————————————————————————————
—-Example 2 : OUTPUT clause with INSERT statement
————————————————————————————————————————
USE AdventureWorks;
GO
----Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table as well use OUTPUT clause to insert
----values in the temp table.
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal
VALUES (2,'SecondVal')
----Clean up time
DROP TABLE TestTable
GO

ResultSet 2:
ID TextVal
——————— ———————
1 FirstVal

(1 row(s) affected)

ID TextVal
——————— ———————
2 SecondVal

————————————————————————————————————————
—-Example 3 : OUTPUT clause into Table with UPDATE statement
————————————————————————————————————————
USE AdventureWorks;
GO
----Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100))
----Insert values in real table
INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')
----Update the table and insert values in temp table using Output clause
UPDATE TestTable
SET TEXTVal = 'NewValue'
OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO

ResultSet 3:
ID_New TextVal_New ID_Old TextVal_Old
——————— ——————— ——————— ———————
1 NewValue 1 FirstVal
2 NewValue 2 SecondVal

ID TextVal
——————— ———————
1 NewValue
2 NewValue

————————————————————————————————————————
—-Example 4 : OUTPUT clause into Table with DELETE statement
————————————————————————————————————————
USE AdventureWorks;
GO
----Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table
INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')
----Update the table and insert values in temp table using Output clause
DELETE
FROM
TestTable
OUTPUT Deleted.ID
, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO

ResultSet 4:
ID TextVal
——————— ———————
1 FirstVal
2 SecondVal

ID TextVal
——————— ———————

If you run all the above four example, you will find that OUTPUT clause is very useful.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – 2005 Query Editor – Microsoft SQL Server Management Studio
Next Post
SQL SERVER – Find Name of The SQL Server Instance

Related Posts

48 Comments. Leave new

  • hi all,
    we are doing migration from sybase to mssql.

    can anyone suggest how can normal user can access all records in syslogins table. user should not be security admin.

    I am thinking to retrieve all records from syslogins with priviledge login into user table and then use user table

    Reply
  • It is possible to make a select from a delete’s output?

    Reply
  • I m getting error of DBNETLIB with the error message ‘General network error. before i never faced such error. sometimes the error is ‘communication link failure’.

    the errors are generated while using application based on VB and SQL on client machines.

    It is not that the error prompts only when huge query is fired from client. the error prompt even just application is open on the client machine and client is not doing anything.

    first of all please any one could help to understand the possible reasons for this error. i have searched on MS sites and also gooled. As per the explanation given all settings are correct on server… but not yet clear….

    AWE is not enabled on SQL server…. SP4 is installed…chimney setting is proper…

    is this machine specific error? any hot fix is required after installing SP4?
    there might be LAN problem?

    Early help would be appreciated.

    Thanks in advance.

    Reply
  • Hi Pinal,

    Very nice article in simple words… Thanks for the explanation but i would like to know what if a table has a trigger attached??

    Will these magic table return the values from trigger or from the table that is been updated?

    Reply
  • Salam all…
    With SQL server 2000, in my application that runs over a network I used some extra work to get the newly inserted ID so that I avoid receiving an ID generated for an other instance of the application on an other computer.
    Now, with SQL server 2005 I felt happy for OUTPUT INSERTED clause, When I tried it in my Delphi-2010 application, it works first time and returns the newly generated ID, but when the command is executed a second time, an error happens with a message: Connection Failed!
    I tried that also with Visual Basic 6, but unfortunately I got the same error. This is the code of VB6:
    ———————————–
    Option Explicit

    Private MyCnn As New ADODB.Connection
    Private MyCmd As New ADODB.Command
    Private MyRS As ADODB.Recordset

    Private Sub Command1_Click()
    Dim ACmdText As String

    ACmdText = “INSERT INTO Customers OUTPUT INSERTED.CustID AS NewID VALUES (‘” _
    & Text1.Text & “‘, ” & Text2.Text
    If Text3.Text = “” Then
    ACmdText = ACmdText & “, NULL)”
    Else
    ACmdText = ACmdText & “, ‘” & Text3.Text & “‘)”
    End If

    MyCmd.CommandType = adCmdText
    MyCmd.CommandText = ACmdText
    Set MyRS = MyCmd.Execute

    Text4.Text = CStr(MyRS(“NewID”))
    End Sub

    Private Sub Form_Load()
    Dim CnnStr As String

    CnnStr = “Provider=SQLOLEDB.1;Integrated Security=SSPI;” _
    & “Persist Security Info=False;” _
    & “Initial Catalog=TestDB;Data Source=.\SQLSRVR2005”
    MyCnn.CursorLocation = adUseClient
    MyCnn.Open “”

    Set MyCmd.ActiveConnection = MyCnn
    End Sub

    Private Sub Form_Unload(Cancel As Integer)
    MyCnn.Close
    End Sub
    ———————————————————–

    Any help if possible, or please direct me to where I can get the solution.

    Thanks in advance, and best regards.

    Reply
  • Thank you Pinal.

    Reply
  • This is really great..
    How could I missed this clause ..
    This really helped me to avoid a lot of coding for me.
    thanks pinal.

    Plahanov

    Reply
  • V Gud excellent and useful to all type of sql developers

    Reply
  • Easy???? How is this “easy” compared to ONE simple line of:
    select @TheNewID = SCOPE_IDENTITY()
    ??????????????????
    I’m not saying better, but how dare you use the word “easy”???!?!?!?!!?

    Reply
    • Nick Gianadda
      March 12, 2013 1:19 am

      It’s “Easy” because it can be used to pull back more than one identity. If you insert 30 records, you get back a table with the 30 new identities.

      Reply
  • Marcos Kenji Naka
    November 19, 2013 1:54 am

    Hi, I found the OUTPUT usage extremely useful, but I am concerned about 1 thing that I haven’t been able to test it out. How big is the cost of using the OUTPUT?

    Reply
  • This articles such a wonderful used for magic table … without using trigger …

    Reply
  • I want to retrieve the recently modified or updated data from one table.There is no any column in that table to check the condition for retrieve the same..so Please help me to do that.Thank you

    Reply
  • how can I track if I update two column
    tab1
    (
    a,
    b,
    c
    )

    updaterecord
    (
    columnname,
    oldvalue,
    newvalue
    )

    update tab1
    set a=1,b=2
    output ‘a’,deleted.a, inserted.a into updated
    where c=1

    How can I maintain record of b column.

    I want to track both the column history.

    Reply
  • Hi Pinal Dive,
    I need a solution for insertion fails
    How to get the specific column value of bulk insertion while insertion fails in sql serve rwithout using cursor .
    Please advice me

    Reply
  • Thank you when ever l seem to have a challenge with syntax, one of your articles always seem to help me out.

    Reply
  • Hi Pinal,
    I usually look for sql solutions posted in your blog, and this is the first time I am writing here.
    I want to return the identity value generated while inserting on a linked server table and assign it to a variable.

    scenario is something like

    INSERT INTO LINK1.mydatabase.dbo.table1
    OUTPUT INSERTED.id
    SELECT * FROM mydatabase.dbo.table1

    This returns an error
    ‘A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.’

    Really Appreciate your work.
    Deep

    Reply
  • Hello Pinal,

    I’m trying to implement auditing in my application using OUTPUT Clause, the problem is that I can audit only one column at a time with INSERT or UPDATE. Let’s say I’m updating a record in the table and updating two or more columns in one UPDATE statement, is there any way I can make an entry in the Audit table for all these columns for the old/new values?

    Reply
  • I have two table without any identity also insert new record any where in table1.
    There are table1 changes their record any time than how I know which record in table1
    is update insert or delete, I know the difference between both table but how to know
    which record have perform insert, update or delete.
    Please give if any solution for that problem.
    Thanks.

    Reply

Leave a Reply

Menu