Microsoft SQL Server 2012 Bible

Summary

Data retrieval and data modification are primary tasks of a database application. This chapter 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 modifications 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 modification and what can go wrong.