3 4
Chapter 20
This chapter covers in detail the Transact-SQL (T-SQL) statements used to modify data that were introduced in earlier chapters; it also describes the T-SQL keywords you can use to control programming flow. You can use these statements and keywords anywhere T-SQL is used—in command lines, scripts, stored procedures, batches, and application programs. In particular, we'll look at the data manipulation statements INSERT, UPDATE, and DELETE (which were touched on in Chapter 13) and the programming constructs IF…ELSE, WHILE, and CASE.
Before we begin our main topic, let's create a table, items, to use for our examples. (We'll create this table in the MyDB database.) The T-SQL commands used to create the items table are shown here:
USE MyDB GO CREATE TABLE items ( item_category CHAR(20) NOT NULL, item_id SMALLINT NOT NULL, price SMALLMONEY NULL, item_desc VARCHAR(30) DEFAULT 'No desc' ) GO
The item_id column is a good candidate for the IDENTITY property. (See the "Adding the IDENTITY Property" section in Chapter 10 for more information about this property.) But because you cannot explicitly insert values into an identity column, we're not using IDENTITY here. This way, we'll have more flexibility with the examples that use the INSERT statement.