Perform, Capture, and Track Data Modifications


In this chapter, I review how to modify data using the Transact-SQL INSERT, UPDATE, and DELETE commands. I’ll review the basics of each command and cover specific techniques such as inserting data from a stored procedure and importing an image file into a table using OPENROWSET BULK functionality. The new SQL Server 2008 features I cover in this chapter include the following:
  • Inserting multiple rows from a single INSERT statement. I’ll also demonstrate using the multiple-row technique to create a query data source in a SELECT clause (without having to create a permanent or temporary table).

  • New assignment operators that allow you to modify a passed data value with minimal coding.

  • The new MERGE command, which allows you to consolidate and apply data modification commands using a single block of code.

  • Storing unstructured data on the file system while maintaining SQL Server transactional control using the new FILESTREAM attribute.

  • Two new options for tracking table data changes using Change Data Capture (CDC) and Change Tracking built-in functionality. Before going into the new features, however, I’ll start the chapter off by reviewing basic INSERT concepts.


File System Update Statement Change Tracking Assignment Operator Source Table 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

Copyright information

© Joseph Sack 2008

Personalised recommendations