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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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




Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems