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)
48 Comments. Leave new
It is possible to make a select from a delete’s output?
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.
:)
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?
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.
Thank you Pinal.
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
V Gud excellent and useful to all type of sql developers
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”???!?!?!?!!?
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.
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?
This articles such a wonderful used for magic table … without using trigger …
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
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.
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
Natarajan – What is the error message you are getting?
Thank you when ever l seem to have a challenge with syntax, one of your articles always seem to help me out.
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
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?
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.