T-SQL OUTPUT Clause in order to INSERT Data in Two Tables in One Command
MS SQL Server 2005 has many t-sql improvements for sql developers.
One of the most enhancements in t-sql introduced by MS SQLSERVER 2005 is the OUTPUT clause in INSERT, UPDATE and DELETE statements.
You can find sample and a definition on T-SQL OUTPUT Clause with INSERT, UPDATE and DELETE command at MS SQL Server 2005 T-SQL OUTPUT Clause Sample Code with Insert, Update, Delete Statements.
T-SQL OUTPUT Clause Example with INSERT into Two SQL Tables
In this example first we will CREATE two sql Tables in a SQL Server 2005 / 2008 database.
You can execute the below create statements for creating sql database tables.
CREATE TABLE Profile (
ProfileId int identity(1,1),
username varchar(10),
fname varchar(10),
lname varchar(10),
age smallint,
)
GO
CREATE TABLE ProfileUsers (
UserId int,
username varchar(10),
pwd varchar(10),
lastlogindate datetime,
active bit
)
Next we will insert data into the first sql table "Profile" using a INSERT INTO command.
And with the OUTPUT Clause in an INSERT Command, we will insert some of the inserted values, and auto identity column values from the first table into an other target table.
Here is the INSERT INTO ... OUTPUT clause example script we will use for inserting data into two tables in one command.
insert into [profile] (
username, fname, lname, age
)
output inserted.profileid, inserted.username into ProfileUsers (UserId, username)
values (
'Developer','Eralper','Yilmaz',35
)
If you select the table rows from two database tables, you will see we have inserted a new record into the first table. At the same time we have inserted a second record into an other sql table using some of the column values (inserted column values) of the first database table.
The OUTPUT Clause in Microsoft SQL Server is one of the most features I love.
Try to use it in your scripts, you will like it also.
For more t-sql examples and tutorials on T-SQL Output Clause with Sparse Null please refer to Sample SQL Column Identity Generator using OUTPUT CLAUSE in T-SQL and SPARSE NULL Data Type