IN THIS CHAPTER
Inserting data from expressions, other result sets, and stored procedures
Updating and deleting data
Mastering the new merge command
Exposing inserted and deleted tables to the DML
Things change. Life moves on. Because the purpose of a database is to accurately represent reality, the data must change along with reality. For SQL programmers, that means inserting, updating, and deleting rows—using the basic data manipulation language (DML) commands. However, these operations aren't limited to writing single rows of data. Working with SQL means thinking in terms of data sets. The process of modifying data with SQL draws on the entire range of SQL Server data-retrieval capabilities—the powerful
SELECT, joins, full-text searches, subqueries, and views.
This chapter is all about modifying data within SQL Server using the
INSERT, UPDATE, DELETE, and
MERGE SQL commands. Modifying data raises issues that need to be addressed, or at least considered. Inserting surrogate primary keys requires special methods. Table constraints may interfere with the data modification. Referential integrity demands that some
DELETE operations cascade to other related tables. This chapter will help you understand these concerns and offer some ways to deal with them. Because these potential obstacles affect
INSERT, UPDATE, MERGE, and, to some degree,
DELETE, they are addressed in their own sections after the sections devoted to the individual commands.
The ACID database ...