March 7, 2021
Hot Topics:

Introductory Transact-SQL

  • By Addison Wesley
  • Send Email »
  • More Articles »

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 customersVALUES(1,'Doe','John','123 Joshua Tree','Plano','TX','75025')INSERT INTO customersVALUES(2,'Doe','Jane','123 Joshua Tree','Plano','TX','75025')INSERT INTO customersVALUES(3,'Citizen','John','57 Riverside','Reo','CA','90120')

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

INSERT INTO ordersVALUES(101,'10/18/90',1,1001,123.45)INSERT INTO ordersVALUES(102,'02/27/92',2,1002,678.90)INSERT INTO ordersVALUES(103,'05/20/95',3,1003,86753.09)INSERT INTO ordersVALUES(104,'11/21/97',1,1002,678.90)

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

INSERT INTO itemsVALUES(1001,'WIDGET A',123.45)INSERT INTO itemsVALUES(1002,'WIDGET B',678.90)INSERT INTO itemsVALUES(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 customersSET 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 customersSET State='CA'

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

UPDATE ordersSET 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 oSET Amount=PriceFROM 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 customersWHERE 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 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.)

Page 3 of 10

This article was originally published on May 7, 2003

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date