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
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s