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
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?
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
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,
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…
https://www.microsoft.com/en-us/download/details.aspx?id=51958
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
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
I mean copy dept1 in TABLE B
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.
Todd,
Was your question ever answered? I am having the same issue. Is there a simple way to do this with the output clause?
Todd,
I would also like to know if you found an answer to this.
Thanks
Hi pinaldave,
This is really good post. Can we use these things inside a transaction as well?
Thanks
Ajith
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
TestTable has 3 Fields but only 2 Values in Values Clause. There needs 3 values.
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
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.
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
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
Any help on the above query is appriciated.
Thanks,
Sourov
Hi Pinal,
Your Post on Output clause …helped me.
Thanks.
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?
Very good explanation, nice article.
Thanks.
In sql server 2000 this code is not working can you tel me what is the problem in this….
Becuase output clause is not supported in versions prior to 2005
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