322
Part II: Building Databases and Working with Data
Summary
Data retrieval and data modifi cation are primary tasks of a database application. This chap-
ter examined the workhorse INSERT, UPDATE, DELETE, and MERGE DML commands and
described how you can use them to manipulate data.Key points in this chapter include the following:■ (^) There are multiple formats for the INSERT command depending on the data’s
source: INSERT...VALUES, INSERT...SELECT, INSERT...EXEC, and
INSERT...DEFAULT.
■ (^) INSERT...VALUES now has row constructors to insert multiple rows with a single
INSERT.
■ (^) INSERT...INTO creates a new table and then inserts the results into the new table.
■ (^) UPDATE always updates only a single table, but it can use an optional FROM clause
to reference other data sources.
■ (^) Using DELETE without a WHERE clause is dangerous.
■ (^) Using UPDATE without a WHERE clause is dangerous.
■ (^) The new MERGE command pulls data from a source table and inserts, updates, or
deletes in the target table depending on the match conditions.
■ (^) INSERT, UPDATE, DELETE, and MERGE can all include an optional OUTPUT clause
that can select data from the query or the virtual inserted and deleted tables. The
result of the OUTPUT clause can be passed to the client, inserted into a table, or
passed to an outer query.
This chapter explained data modifi cations assuming all goes well, but several conditions
and situations can conspire to block the INSERT, UPDATE, DELETE, or MERGE. The next
chapter looks at the dark side of data modifi cation and what can go wrong.
c12.indd 322c12.indd 322 7/30/2012 4:42:44 PM7/30/2012 4:42:44 PM
http://www.it-ebooks.info