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 (http://blog.SQLAuthority.com)

About these ads

44 thoughts on “SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE

  1. Hey. thanks for pointing this clause out. It would be very helpful, if it worked for me. I am coding in C# and every time i go to insert an item in the table, the code will fail if “output inserted.*” is in the command text. i’ve tried moving around the clause as i’ve seen on msdn. i didn’t think that would help, and … it didn’t.

    so, is there some special server configuration that i need? any helpful hints?

  2. Hi Pinal,
    Can we use Trigger in Sql server 2005 to perform:
    1. Insert
    2. Update
    3. Delete
    Processes with the FK. if yes.
    Please give an example for that.

    regards
    shyam

  3. HI,

    Can you please give one example to create Trigger on Row level and if there is any change in any field, insert the whole row in into the table.

    Thanks,

  4. Create an history table “table1″ to keep track the details of any changes in table “table2″

    in this below example i am inserting the record into table “table1″ when ever any insert or update is happened in “table1″

    Create trigger [tg_Sample] on [dbo].[table1] for Insert,Update as

    Insert into table2 (col1,col2,col3,col4)
    select col1,col2,col3,col4 from inserted

    This link will be helpfull to you…

    http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx

  5. hi every one,
    i need a help. i want to connect more than 30 database at the time to view the datails for the chairman of our group of company. if you know the coding please help me.
    thank you
    Sukitha

    • CREATE TABLE TestTable (AutoID INT IDENTITY(1,1),ID INT, TEXTVal VARCHAR(100))
      —-Creating temp table to store ovalues of OUTPUT clause
      DECLARE @TmpTable TABLE (AutoID INT,ID INT, TEXTVal VARCHAR(100))
      —-Insert values in real table as well use OUTPUT clause to insert
      —-values in the temp table.
      INSERT TestTable (AutoID,ID, TEXTVal)
      OUTPUT Inserted.AutoID,Inserted.ID, Inserted.TEXTVal INTO @TmpTable
      VALUES (1,’FirstVal’)
      INSERT TestTable (AutoID,ID, TEXTVal)
      OUTPUT Inserted.AutoID,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

  6. Hi pinal,
    I ejoy reading your blog as its neat and gives me a exact solution,

    I am stumbled upon a small problem..

    I have two tables which i want to update but the table B has a foreingn key in Table A which is Identity column,

    Now if i do a single insert i am fine with output clause but how do i tackle a multiple insert.

    Eg: table A Empid,Empname,deptid
    table B, deptid,deptname,

    now If i want to copy dept1 and insert into table B
    Say dept1 has 100 employees,
    My requirement is COPY the whole department with employees and create a duplicate dept in Table A
    how do i do it. please help

  7. Hi,
    Thought I’d ask an expert this question. I sometimes have the need to do deep-copies involving multiple tables where I need to maintain a mapping between IDs of rows being copied from and IDs of the new rows. I usually have to resort to doing the inserts one by one using cursors in order to maintain these mappings, but I thought the OUTPUT clause might allow me to do it in sets. But now I’m not so sure I can get that “from” ID. Below is a simplification of what I’d like to do:

    INSERT Thing (Name)
    OUTPUT Inserted.ID AS ToID, ??? AS FromID
    SELECT Name
    FROM Thing
    WHERE …

    I can’t get this to work because it seems that INSERTED columns are the only ones I can output, and hence I can’t get the from ID. Can you confirm that this is correct? Thank you very much for your help.

  8. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER - 2008 - SCOPE_IDENTITY Bug with Multi Processor Parallel Plan and Solution Journey to SQL Authority with Pinal Dave

  10. Hi ,
    We can’t insert AutoID values to temp table? I tried following, but giving errors,
    ———————–
    CREATE TABLE TestTable (AutoID INT IDENTITY(1,1),ID INT, TEXTVal VARCHAR(100))
    —-Creating temp table to store ovalues of OUTPUT clause
    DECLARE @TmpTable TABLE (AutoID INT,ID INT, TEXTVal VARCHAR(100))
    —-Insert values in real table as well use OUTPUT clause to insert
    —-values in the temp table.
    INSERT TestTable (AutoID,ID, TEXTVal)
    OUTPUT Inserted.AutoID,Inserted.ID, Inserted.TEXTVal INTO @TmpTable
    VALUES (1,’FirstVal’)
    INSERT TestTable (AutoID,ID, TEXTVal)
    OUTPUT Inserted.AutoID,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
    ————————–

    Thanks
    Ajith

  11. Hi Ajit,

    u can insert AutoID value to temp table, use following code,

    –Creating a TestTable
    CREATE TABLE TestTable (AutoID INT IDENTITY(1,1),ID INT, TEXTVal VARCHAR(100))
    –Creating a Temp Table
    DECLARE @TmpTable TABLE (AutoID INT,ID INT, TEXTVal VARCHAR(100))

    –Insert value in TestTable and get value in Temp Table
    INSERT TestTable (ID, TEXTVal)
    OUTPUT Inserted.AutoID,Inserted.ID, Inserted.TEXTVal INTO @TmpTable
    VALUES (1,’FirstVal’)

    INSERT TestTable (ID, TEXTVal)
    OUTPUT Inserted.AutoID,Inserted.ID, Inserted.TEXTVal INTO @TmpTable
    VALUES (2,’SecondVal’)

    select * from @TmpTable

    AutoID ID TEXTVal
    1 1 FirstVal
    2 2 SecondVal

    ————————————————————–

    we can’t Give The Autogenrate Column name in insert column, like insert TestTable(AutoID) that is wrong.

    Thanks

    Praveen Agrawal

  12. Hi, i was looking for what you showed in example 2, for like 3 hours… tenths of websites.

    I needed it for my identity, since scope_identity() is bugged.

    with your help plus something i read elsewhere now i can retrieve the identity i needed back to my code, and use it with my typed dataset and adapters.

    [For Reference]
    I just created a query for my table adapter with my equivalent for example 2, and then in the method properties, changed execute mode from NonQuery to Scalar. That way the method returns the new identity (Convert needed).
    ————–

    Thanks a lot.

  13. Pinal,

    I have a application that is multi-threaded that is doing hundreds of inserts a minute. I want to use the OUTPUT clause to return me the identity field from each insert.

    Have you seen any problems where with getting the unique identity field back for a given insert when hundreds of inserts are being execute at the same time.

    Thanks Tim

  14. Pinal ,

    output clause is great when we need to do secondary jobs on all or subset of the columns being inserted in the main table. But, how can we update two tables with no overlapping columns other than a common pk column (e.g. specialization & generalization tables). My idea is to update both the tables in one read of a temp table and update two tables at one go. here is the DML that fails.

    INSERT INTO payout (
    — payout_id (identity column),
    payoutType_id,Start_Date
    )
    OUTPUT INSERTED.payout_id , source
    INTO payoutBonus(payout_id,source)
    SELECT payoutType_id,Start_Date, source
    FROM @temp_payout_table

    Thanks

    Sourov

  15. Pinal, It’s a great solution and we have used it. Recently we had some cross server(remote) queries. I tried it this on a remote server and its is saying “a remote table cannot be used as a DML target in a statement which includes output caluse or nested DML statement”. How could it be used with a remote server?

  16. 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

  17. 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.

  18. 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?

  19. Pingback: SQL SERVER – Reasons for Using Output Clause – Quiz – Puzzle – 10 of 31 « SQL Server Journey with SQL Authority

  20. Pingback: SQL SERVER – A Quick Look at Logging and Ideas around Logging « SQL Server Journey with SQL Authority

  21. 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.

  22. 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

  23. 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”???!?!?!?!!?

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

  25. 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?

  26. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s