Introductory Transact-SQL (part 3)

By Addison Wesley

Inserting Data

Use the Transact-SQL INSERT statement to add data to a table, one row at a time. Let's explore this by adding some test data to the customers table. Enter the following SQL commands to add three rows to customers:

INSERT INTO customers
VALUES(1,'Doe','John','123 Joshua Tree','Plano','TX','75025')
INSERT INTO customers
VALUES(2,'Doe','Jane','123 Joshua Tree','Plano','TX','75025')
INSERT INTO customers
VALUES(3,'Citizen','John','57 Riverside','Reo','CA','90120')

Now, add four rows to the orders table using the same syntax:

INSERT INTO orders
VALUES(101,'10/18/90',1,1001,123.45)

INSERT INTO orders
VALUES(102,'02/27/92',2,1002,678.90)

INSERT INTO orders
VALUES(103,'05/20/95',3,1003,86753.09)

INSERT INTO orders
VALUES(104,'11/21/97',1,1002,678.90)

Finally, insert three rows into the items table like so:

INSERT INTO items
VALUES(1001,'WIDGET A',123.45)

INSERT INTO items
VALUES(1002,'WIDGET B',678.90)

INSERT INTO items
VALUES(1003,'WIDGET C',86753.09)

Notice that none of these INSERTs specifies a list of fields, only a list of values. The INSERT command defaults to inserting a value for all columns in order, though you could have specified a column list for each INSERT using syntax like this:

INSERT INTO items (ItemNumber, Price)
VALUES(1001,123.45)

Also note that it's unnecessary to follow the table's column order in a column list; however, the order of values you supply must match the order of the column list. Here's an example:

INSERT INTO items (Price, ItemNumber)
VALUES(123.45, 1001)

One final note: The INTO keyword is optional in Transact-SQL. This deviates from the ANSI SQL standard and from most other SQL dialects. The syntax below is equivalent to the previous query:

INSERT items (Price, ItemNumber)
VALUES(123.45, 1001)

Updating Data

Most people eventually want to change the data they've loaded into a database. The SQL UPDATE command is the means by which this happens. Here's an example:

UPDATE customers
SET Zip='86753-0900'
WHERE City='Reo'

Depending on the data, the WHERE clause in this query might limit the UPDATE to a single row or to many rows. You can update all the rows in a table by omitting the WHERE clause:

UPDATE customers
SET State='CA'

You can also update a column using columns in the same table, including the column itself, like so:

UPDATE orders
SET Amount=Amount+(Amount*.07)

Transact-SQL provides a nice extension, the SQL UPDATE command, that allows you to update the values in one table with those from another. Here's an example:

UPDATE o
SET Amount=Price
FROM orders o JOIN items i ON (o.ItemNumber=i.ItemNumber)

Deleting Data

The SQL DELETE command is used to remove data from tables. To delete all the rows in a table at once, use this syntax:

DELETE FROM customers

Similarly to INSERT, the FROM keyword is optional. Like UPDATE, DELETE can optionally include a WHERE clause to qualify the rows it removes. Here's an example:

DELETE FROM customers
WHERE LastName<>'Doe'

SQL Server provides a quicker, more brute-force command for quickly emptying a table. It's similar to the dBASE ZAP command and looks like this:

TRUNCATE TABLE customers

TRUNCATE TABLE empties a table without logging row deletions in the transaction log. It can't be used with tables referenced by FOREIGN KEY constraints, and it invalidates the transaction log for the entire database. Once the transaction log has been invalidated, it can't be backed up until the next full database backup. TRUNCATE TABLE also circumvents the triggers defined on a table, so DELETE triggers don't fire, even though, technically speaking, rows are being deleted from the table. (See Chapter 4, "DDL Insights," for more information.)