Sql server® 2012 t-sql fundamentals

INSERT

 with 

OUTPUT

An example of an INSERT statement for which the OUTPUT clause can be useful is when you need to 

insert a row set into a table with an identity column, and you need to get back all identity values that 

were generated. The SCOPE_IDENTITY function returns only the very last identity value that was gen-

erated by your session; it doesn’t help you much in obtaining all identity values that were generated 

by an insert of a row set. The OUTPUT clause makes the task very simple. To demonstrate the tech-

nique, first create a table called T1 with an identity column called keycol and another column called 

datacol by running the following code.

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1; 

 

CREATE TABLE dbo.T1 

( 

  keycol  INT          NOT NULL IDENTITY(1, 1) CONSTRAINT PK_T1 PRIMARY KEY, 

  datacol NVARCHAR(40) NOT NULL 

);

www.it-ebooks.info

---