Monday, June 18, 2012

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;

if exists
       select *
       from sys.tables
       where name = 'TestDataTable'
       drop table TestDataTable;
create table TestDataTable
       id int identity(1, 1) not null,
       SomeString nvarchar(128) not null,
       AnotherInt int not null

-- show the output of the inserted data to the client
insert into TestDataTable(SomeString, AnotherInt)
output, inserted.SomeString, inserted.AnotherInt
       ('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

-- output the deleted data to the temp table
delete from TestDataTable
output deleted.*
into #DeletedData
where AnotherInt in (34, 49);

select *
from #DeletedData;


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

No comments:

Post a Comment