Lesson 2: Modifying Data | Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit

You can insert new rows into a table using the INSERT statement, modify data in existing rows in a table using the UPDATE statement, and delete existing rows from a table using the DELETE statement. When you add rows to an existing table, SQL Server can insert default values for you, allowing you to enter partial data.

After this lesson, you will be able to:

Write INSERT, DELETE, and UPDATE statements to modify data in tables

Estimated lesson time: 60 minutes

Inserting Rows

The INSERT statement adds rows to a table. The partial syntax of the INSERT statement is as follows:

    INSERT [INTO] table_name [(column_list)]     {VALUES ({DEFAULT | NULL | expression}[, n])} | DEFAULT VALUES

Use the INSERT statement with the VALUES clause to add rows to a table. When you insert rows, consider the following facts and guidelines:

Use the column_list to specify columns that will store each incoming value. You must enclose the column_list in parentheses and delimit column names in the list with commas. If you are supplying values for all columns, using the column_list is optional.

Specify the data that you want to insert by using the VALUES clause. The VALUES clause is required. The VALUES clause must specify a value for every column in the table or for every column in the column_list, if a column_list was specified. Values in the VALUES clause must be enclosed in parentheses and delimited with commas.

The order of the values in the VALUES clause must be the same as the column order in the table or in the column_list. The data types of new data must correspond to the data types of the columns in the table. Many data types have an associated entry format. For example, character data and dates must be enclosed in single quotation marks.

An INSERT statement fails if the data values violate a constraint or rule.

To make your script clearer, you should always specify a column_list.

Example

The following example adds Pecos Coffee Company as a new customer.

 USE Northwind  INSERT Customers        (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) VALUES ('PECOF', 'Pecos Coffee Company','Michael Dunn', 'Owner',         '1900 Oak Street', 'Vancouver', 'BC', 'V3F 2K1', 'Canada', '(604) 555-3392', '(604) 555-7293')

You can verify that Pecos Coffee Company has been added to the Customers table by executing the following statements.

 USE Northwind  SELECT CompanyName, ContactName  FROM Customers  WHERE CustomerID = 'PECOF'

Result

 CompanyName                                 ContactName                      ---------------------------------------- ------------------------------   Pecos Coffee Company                        Michael Dunn  (1 row(s) affected)

Exercise 1: To Insert Rows

In this exercise, you will insert rows into the item and copy tables to represent a book in the library collection.

To write an INSERT statement

Open SQL Server Query Analyzer, and log in to the (local) server with Microsoft Windows NT authentication.

In the DB list, click library to make library the current database.

Insert two rows into the item table for title number 8, The Cherry Orchard. Specify the column names for which you are supplying values. Use the column values listed in the following table for the new rows:

 USE library

isbn	title_no	Cover	loanable	translation
10001	8	HARDBACK	Y	ENGLISH
10101	8	SOFTBACK	Y	ENGLISH

 INSERT item (isbn, title_no, cover, loanable, translation)              VALUES (10001, 8, 'HARDBACK', 'Y', 'ENGLISH')  INSERT item (isbn, title_no, cover, loanable, translation)              VALUES (10101, 8, 'SOFTBACK', 'Y', 'ENGLISH')

Insert a row into the copy table for the hardback item that you added in step 3, using the values listed in the following table:

isbn	copy_no	title_no	on_loan
10001	1	8	N

 USE library  INSERT copy (isbn, copy_no, title_no, on_loan)  VALUES (10001, 1, 8, 'N')

Write and execute a query that returns the translation column for the items that you inserted in step 3.

 USE library  SELECT translation  FROM item  WHERE isbn = 10001 OR isbn = 10101

Your result should look similar to the following result set:

 translation   -----------   ENGLISH   ENGLISH  (1 row(s) affected)

Inserting Data Using Default Values

When you insert rows into a table, you can save time in entering values by using the DEFAULT or DEFAULT VALUES keywords with the INSERT statement.

DEFAULT Keyword

When a table has default constraints or when a column has a default value, use the DEFAULT keyword in the INSERT statement to have SQL Server supply the default value for you.

When you use the DEFAULT keyword, consider the following facts and guidelines:

SQL Server inserts a null value for columns that allow null values and do not have default values.

If you use the DEFAULT keyword and the columns do not have default values or allow null values, the INSERT statement fails.

Enter a null value explicitly by typing NULL without single quotation marks.

You cannot use the DEFAULT keyword with a column that has the IDENTITY property (an automatically assigned, incremented value). Therefore, do not list columns with an IDENTITY property in the column_list or VALUES clause.

SQL Server inserts the next appropriate value for columns that are defined with the timestamp data type.

Example

This example inserts a new row for the Kenya Coffee Company in the Shippers table. The Shippers.ShipperID column has an IDENTITY property and is therefore not included in the column or values lists. The Phone column allows null values, so DEFAULT can be specified for the Phone column value in the values list.

 USE Northwind  INSERT Shippers (CompanyName, Phone)  VALUES ('Kenya Coffee Co.', DEFAULT)

You can verify that Kenya Coffee Company has been added to the Shippers table by executing the following statements.

 USE Northwind  SELECT *  FROM Shippers  WHERE CompanyName = 'Kenya Coffee Co.'

Result

 ShipperID   CompanyName                            Phone                      ----------- ----------------------------------- ------------------------   4           Kenya Coffee Co.                       NULL  (1 row(s) affected)

DEFAULT VALUES Keyword

Use the DEFAULT VALUES keyword to insert an entire row into a table without specifying values for any columns.

Inserting a row with the DEFAULT VALUES keyword is useful in an application in which you want to show a new identity number to a user while the user is adding a new record. Using this technique, you are able to show the identity number before the user has added the new row, because the row is actually added in advance, with default values. After adding the new row, you can display the new identity number and other default values to the user when he or she begins editing the values for the new row. When the user has finished entering the new values, use the UPDATE statement to update the row with the new values.

When you use the DEFAULT VALUES keyword, consider the following facts and guidelines:

SQL Server inserts a null value for columns that allow null values and do not have a default value.

If you use the DEFAULT VALUES keyword and the columns do not have default values or allow null values, the INSERT statement fails.

SQL Server inserts the next appropriate value for columns with an IDENTITY property or a timestamp data type.

Use the DEFAULT VALUES keyword to generate sample data and populate tables with default values.

Inserting Partial Data

If a column has a default value or accepts null values, you can omit the column from an INSERT statement, rather than specifying the DEFAULT keyword. SQL Server automatically inserts the values.

When you insert partial data, consider the following facts and guidelines:

Specify the columns for which you are providing a value in the column_list. The data in the VALUES clause corresponds to the specified columns. Unnamed columns are filled in as though they had been named and a default value had been supplied. If an unnamed column does not have a default value, a null value is inserted if the column allows null values.

In the column_list, do not specify columns that have an IDENTITY property or that allow default or null values.

Example

This example adds the company Fitch & Mather as a new shipper in the Shippers table. Data is not entered for the ShipperID column (because it has the IDENTITY property) nor for the Phone column (because it allows null values). The ShipperID and Phone columns are also omitted from the column_list, and the DEFAULT keyword is omitted from the values list.

USE Northwind  INSERT Shippers (CompanyName)  VALUES ('Fitch & Mather')

You can verify that Fitch & Mather has been added to the Shippers table by executing the following syntax.

 USE Northwind  SELECT *  FROM Shippers  WHERE CompanyName = 'Fitch & Mather'

Result

 ShipperID   CompanyName                            Phone                      ----------- ----------------------------------- ------------------------   5           Fitch & Mather                         NULL  (2 row(s) affected)

Exercise 2: To Insert Rows by Using Default Values

In this exercise, you will add new titles by adding rows to the title table in the library database. Supply values only for those columns that require them.

To write an INSERT statement that uses the DEFAULT keyword

Execute the sp_help system-stored procedure to determine which columns in the title table allow null values. You do not have to supply values for columns that allow null values, have default values, or have the IDENTITY property.

 USE library  EXEC sp_help title

Insert a row into the title table for the book, Zen and the Art of Archery, by D.T. Suzuki. Use the DEFAULT keyword for columns that allow null values or that have default values. Do not supply a value for the title_no column, because this column has the IDENTITY property.

 USE library  INSERT title (title, author, synopsis)  VALUES ('Zen and the Art of Archery', 'D.T. Suzuki', DEFAULT)

Write and execute a query that uses the @@IDENTITY function to determine the title_no of the title that you added in step 2.

 USE library  SELECT @@IDENTITY

Write and execute a query to verify that the new title was added to the title table. Use the value that you obtained in step 3 for the title_no column.

 USE library  SELECT title_no, title, author  FROM title  WHERE title_no = @@IDENTITY

Your result should look similar to the following result set.

 title_no    title                           author                            ----------- ---------------------------- -------------------   51          Zen and the Art of Archery    D.T. Suzuki  (1 row(s) affected)

Insert a row into the title table for the title, Love in the Time of Cholera, by G.G. Marquez. Specify a column_list and values for columns that do not allow null values or have default values.

 USE library  INSERT title (title, author)  VALUES ('Love in the Time of Cholera', 'G.G Marquez')

Write and execute a query to verify that the new title was added to the title table.

 SELECT title_no, title, author  FROM title  WHERE title_no = @@IDENTITY

Your result should look similar to the following result set.

 title_no    title                             author                            ----------- ------------------------------ ---------------   52          Love in the Time of Cholera     G.G Marquez  (1 row(s) affected)

Exercise 3: To Insert Rows by Using Default Values

In this exercise, you will use a sample table called deftable that has been created in the library database. You will insert a row into the deftable table using the DEFAULT VALUES keyword. Then you will write and execute a query to verify that the new row was added to the table. The table has the characteristics listed in the following table.

Column Name	Datatype	IDENTITY property?	Default	Allows NULL?
cust_id	Int	Yes (100,5)	None	No
name	char(10)	No	None	Yes
region	char(10)	No	'Northwest'	No

To write an INSERT statement that uses the DEFAULT VALUES keyword

Insert a new row into the deftable table without specifying the column names. Use the DEFAULT VALUES keyword with the INSERT statement.

 USE library  INSERT deftable DEFAULT VALUES

Write and execute a query to verify that the new row was added to the deftable table.

 USE LIBRARY  SELECT *  FROM deftable

Your result should look similar to the following result set.

 cust_id     name       region       ----------- ---------- ----------   100         NULL       Northwest   (1 row(s) affected)

Deleting Rows

The DELETE and TRUNCATE TABLE statements remove rows from tables.

DELETE Statement

Use the DELETE statement to remove one or more rows from a table. The partial syntax of the DELETE statement is as follows:

    DELETE [FROM] table_name     WHERE <search_condition>

When you use the DELETE statement, consider the following facts:

SQL Server deletes from the table all rows that match the WHERE clause search condition.

SQL Server deletes all rows from a table if you do not include a WHERE clause in the DELETE statement.

Each deleted row is logged in the transaction log.

Example 1

The following example deletes the shipper called Fitch & Mather from the Shippers table.

 USE Northwind  DELETE Shippers  WHERE CompanyName = 'Fitch & Mather'

TRUNCATE TABLE Statement

Use the TRUNCATE TABLE statement to delete all rows from a table. The syntax of the TRUNCATE TABLE statement is as follows:

    TRUNCATE TABLE table_name

When you use the TRUNCATE TABLE statement, consider the following facts:

SQL Server deletes all rows but retains the table structure and its associated objects such as indexes and constraints.

The TRUNCATE TABLE statement executes more quickly than the DELETE statement because SQL Server logs only the deallocation of data pages, not the deletion of each row.

If a table has an IDENTITY column, the TRUNCATE TABLE statement resets the IDENTITY seed value. If you use the DELETE statement to delete all rows from a table that has an IDENTITY column, the IDENTITY seed value is not reset.

Example 2

The following example removes all data from the Orders table.

 USE Northwind  TRUNCATE TABLE Orders

Exercise 4: To Delete Rows

In this exercise, you will use the DELETE statement to remove a book with an ISBN of 10101 and a title number of 8 from the item table in the library database.

To write a DELETE statement

Write and execute a query that deletes a row from the item table. Delete the paperback copy (ISBN 10101) of The Cherry Orchard (title_no 8).

 USE library  DELETE FROM item  WHERE isbn = 10101 AND title_no = 8

Updating Rows

The UPDATE statement modifies existing data. The partial syntax of the UPDATE statement is as follows:

    UPDATE table_name       SET {column_name = {expression | DEFAULT}}[, n]      [WHERE <search_condition>]

Use the UPDATE statement to change single rows, groups of rows, or all of the rows in a table. When you update rows, consider the following facts and guidelines:

Specify which rows to update with the WHERE clause.

Specify the new values with the SET clause. In the SET clause, list one or more assignments delimited by commas, setting a column name from the table equal to a new value.

The data types of the input values must be the same as the data types that are defined for the columns.

SQL Server does not update rows that violate any integrity constraints. The changes do not occur, and the statement is rolled back.

You can change the data in only one table at a time.

You can assign the value of an expression to a column. For example, an expression can be a calculation, such as (UnitPrice * 2), or the addition of two values from columns, such as (Amount + Tax).

Example

The following example adds 10 percent to the current prices of all Northwind Traders products.

 USE Northwind  UPDATE Products  SET UnitPrice = (UnitPrice * 1.1)

Exercise 5: To update rows

In this exercise, you will modify the last name of member number 509 in the member table in the library database.

To write an INSERT statement

Execute the following query to find the last name of the member with a member number of 509.

 USE library  SELECT lastname  FROM member  WHERE member_no = 509

Your results will be similar to the following.

 lastname          ---------------   Rudd  (1 row(s) affected)

Change the last name of member number 509 in the member table to a different one of your choice.

 USE library  UPDATE member  SET lastname = 'Benson'  WHERE member_no = 509

Execute the following query to check that the last name of member number of 509 has been changed.

 USE library  SELECT lastname  FROM member  WHERE member_no = 509

Your results will be similar to the following.

 lastname          ---------------   Benson  (1 row(s) affected)

Performance Considerations

When you perform basic queries, you should consider some of the issues that impact the performance of SQL Server:

Use positive rather than negative predicates. Negative predicates such as NOT BETWEEN, NOT IN, and IS NOT NULL slow data retrieval because all rows are evaluated.

Avoid using the LIKE operator if you can write a more specific query. Data retrieval may be slower when you use the LIKE operator.

Use exact matches or ranges in the search condition when possible.

Data retrieval may be slower if you use the ORDER BY clause, because SQL Server must determine the result set before it returns the first row.

Lesson Summary

You use the INSERT, UPDATE, and DELETE statements to modify data in SQL Server tables. When inserting data into a table, values for some columns may be omitted if the columns have default constraints defined for them or they allow null values. When deleting all the rows from a table, use the TRUNCATE TABLE statement rather than the DELETE statement.