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
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:
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.
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) |
In this exercise, you will insert rows into the item and copy tables to represent a book in the library collection.
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') |
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') |
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) |
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.
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:
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) |
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:
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:
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) |
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.
USE library EXEC sp_help title |
USE library INSERT title (title, author, synopsis) VALUES ('Zen and the Art of Archery', 'D.T. Suzuki', DEFAULT) |
USE library SELECT @@IDENTITY |
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) |
USE library INSERT title (title, author) VALUES ('Love in the Time of Cholera', 'G.G Marquez') |
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) |
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 |
USE library INSERT deftable DEFAULT VALUES |
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) |
The DELETE and TRUNCATE TABLE statements remove rows from tables.
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:
Example 1
The following example deletes the shipper called Fitch & Mather from the Shippers table.
USE Northwind DELETE Shippers WHERE CompanyName = 'Fitch & Mather' |
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:
Example 2
The following example removes all data from the Orders table.
USE Northwind TRUNCATE TABLE Orders |
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.
USE library DELETE FROM item WHERE isbn = 10101 AND title_no = 8 |
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:
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) |
In this exercise, you will modify the last name of member number 509 in the member table in the library database.
USE library SELECT lastname FROM member WHERE member_no = 509 |
Your results will be similar to the following.
lastname --------------- Rudd (1 row(s) affected) |
USE library UPDATE member SET lastname = 'Benson' WHERE member_no = 509 |
USE library SELECT lastname FROM member WHERE member_no = 509 |
Your results will be similar to the following.
lastname --------------- Benson (1 row(s) affected) |
When you perform basic queries, you should consider some of the issues that impact the performance of SQL Server:
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.