Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 281

Suppose you want to insert into T1 the result of a query against the HR.Employees table. To return 

all newly generated identity values from the INSERT statement, simply add the OUTPUT clause and 

specify the attributes you want to return.

INSERT INTO dbo.T1(datacol) 

  OUTPUT inserted.keycol, inserted.datacol 

    SELECT lastname 

    FROM HR.Employees 

    WHERE country = N'USA';

This statement returns the following result set.

keycol      datacol 

----------- --------- 

1           Davis 

2           Funk 

3           Lew 

4           Peled 

5           Cameron 

 

(5 row(s) affected)

As you can guess, you can use a similar technique to return sequence values generated for an 

INSERT statement by the NEXT VALUE FOR function (either directly or in a default constraint).

As I mentioned earlier, you can also direct the result set into a table. The table can be a real table, 

a temporary table, or a table variable. When the result set is stored in the target table, you can 

manipulate the data by querying that table. For example, the following code declares a table vari-

able called @NewRows, inserts another result set into T1, and directs the result set returned by the 

OUTPUT clause into the table variable. The code then queries the table variable just to show the data 

that was stored in it.

DECLARE @NewRows TABLE(keycol INT, datacol NVARCHAR(40)); 

 

INSERT INTO dbo.T1(datacol) 

  OUTPUT inserted.keycol, inserted.datacol 

  INTO @NewRows 

    SELECT lastname 

    FROM HR.Employees 

    WHERE country = N'UK'; 

 

SELECT * FROM @NewRows;

This code returns the following output showing the contents of the table variable.

keycol      datacol 

----------- ------------- 

6           Buck 

7           Suurs 

8           King 

9           Dolgopyatova 

 

(4 row(s) affected)

www.it-ebooks.info

---