Modify Data by Using INSERT, UPDATE, and DELETE Statements

(This is part of the Study Guide series, 70-457)

Microsoft’s Measured Skill description: This objective may include but is not limited to: given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement

What I see:

·         OUTPUT statement

OUTPUT Statement

                The OUTPUT clause can be used to pipe affected data from a corresponding DML statement (INSERT, UPDATE, DELETE) or MERGE.  Through the use of this clause, the data can be saved to a table (user table, table var, temp table, etc.) or output to the host.  The below example shows two of the many ways you can utilize the OUTPUT clause:

use MeasureMySkills;

go

if exists

(

       select *

       from sys.tables

       where name = 'TestDataTable'

)

       drop table TestDataTable;

go

create table TestDataTable

(

       id int identity(1, 1) not null,

       SomeString nvarchar(128) not null,

       AnotherInt int not null

);

go

-- show the output of the inserted data to the client

insert into TestDataTable(SomeString, AnotherInt)

output inserted.id, inserted.SomeString, inserted.AnotherInt

values

       ('hello', 34),

       ('goodbye', 49),

       ('hola', 60),

       ('adios', 78);

-- create temp table to hold deleted data

create table #DeletedData

(

       id int not null,

       SomeString nvarchar(128) not null,

       AnotherInt int not null

);

go

-- output the deleted data to the temp table

delete from TestDataTable

output deleted.*

into #DeletedData

where AnotherInt in (34, 49);

select *

from #DeletedData;

References

·         BOL reference on the OUTPUT Clause

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.