SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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.

  ProfileId int identity(1,1),
  username varchar(10),
  fname varchar(10),
  lname varchar(10),
  age smallint,
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 (

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

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.