Lesson 3:Modifying Data in a SQL Server Database

3 4

An essential component of a database system is the capability to modify data that is stored within that system. SQL Server supports a number of methods for adding new rows of data to tables in a SQL Server database, changing the data in existing rows, and deleting rows. In this lesson, you will learn how to add, modify, and delete data in a SQL Server database.

---

After this lesson, you will be able to:

• Insert data into a SQL Server database.
• Modify data in a SQL Server database.
• Delete data from a SQL Server database.

Estimated lesson time: 35 minutes

---

Inserting Data into a SQL Server Database

SQL Server includes several methods for adding data to a database:

• The INSERT statement
• The SELECT...INTO statement
• The WRITETEXT statement and several database Application Programming Interface (API) options, which you can use to add ntext, text, or image data to a row
• The bulk copy component for inserting large numbers of rows

NOTE

---

An INSERT statement works on views as well as on tables (with some restrictions).

Using the INSERT Statement to Add Data

The INSERT statement adds one or more new rows to a table. In a simplified treatment, an INSERT statement takes the following form:

INSERT [INTO] table_or_view [(column_list)] data_values

The statement causes the data values (data_values) to be inserted as one or more rows into the named table or view. The list of column names (column_list), which are separated by commas, can be used to specify the columns for which data is supplied. If no columns are specified, all of the columns in the table or view receive data. If only a partial list is specified, a null value or the default value (if a DEFAULT definition exists) is inserted into any column not named in the list. All columns not specified in the column list must either allow null values or have a default assigned.

In addition, an INSERT statement does not specify values for the following types of columns, because SQL Server automatically generates those values:

• Columns with an IDENTITY property
• Columns with a DEFAULT definition that uses the NEWID() function
• Computed columns

NOTE

---

The INTO keyword in an INSERT statement is optional and is used only to make code clearer.

The data values supplied must match the column list. The number of data values must be the same as the number of columns, and the data type, precision, and scale of each data value must match those of the corresponding column.

When defining an INSERT statement, you can use a VALUES clause to specify the data values for one row or you can use a SELECT subquery to specify the data values for one or more rows.

Using an INSERT...VALUES Statement to Add Data

A VALUES clause enables you to specify the values for one row of a table. The values are specified as a comma-separated list of scalar expressions whose data type, precision, and scale must be the same as or implicitly convertible to the corresponding column in the column list. If a column list is not specified, the values must be specified in the same sequence as the columns in the table or view.

For example, suppose you create the following table in the Pubs database:

 USE Pubs CREATE TABLE NewBooks       (       BookID INT IDENTITY(1,1) NOT NULL,       BookTitle VARCHAR(80) NOT NULL,       BookType CHAR(12) NOT NULL             CONSTRAINT [booktype_df] DEFAULT ('Undecided'),       PubCity VARCHAR(50) NULL       ) 

Once you have created the table, you then decide to add a row of data to that table. The following INSERT statement uses a VALUES clause to insert a new row into the NewBooks table:

 USE Pubs INSERT INTO NewBooks (BookTitle, PubCity) VALUES ('Life Without Fear', 'Chicago') 

In this statement, values are being defined for the BookTitle column and the PubCity column. You do not need to include the BookID column in the INSERT statement, however, because the BookID column is defined with the IDENTITY property. Therefore, values for that column are generated automatically. In addition, because you did not define a value for the BookType column, SQL Server automatically inserts the default value (Undecided) into the column when you run the INSERT statement.

Using a SELECT Subquery to Add Data

You can use a SELECT subquery in the INSERT statement to add values to a table from one or more other tables or views. A subquery enables you to add more than one row at a time.

NOTE

---

A SELECT subquery in an INSERT statement is used to add subsets of existing data to a table, whereas a VALUES clause is used in an INSERT statement to add new data to a table.

The following INSERT statement uses a SELECT subquery to insert rows into the NewBooks table:

 USE Pubs    INSERT INTO NewBooks (BookTitle, BookType)           SELECT Title, Type           FROM Titles           WHERE Type = 'mod_cook' 

This INSERT statement uses the output of the SELECT subquery to provide the data that will be inserted into the NewBooks table.

Using a SELECT...INTO Statement to Add Data

The SELECT INTO statement enables you to create a new table and populate it with the result set of the SELECT statement. Lesson 1 discusses the INTO clause of a SELECT statement in more detail.

Adding ntext, text, or image Data to Inserted Rows

SQL Server includes several methods for adding ntext, text, or image values to a row:

• You can specify relatively short amounts of data in an INSERT statement in the same way that you specify char, nchar, or binary data.
• You can use a WRITETEXT statement, which permits non-logged, interactive updating of an existing text, ntext, or image column. This statement completely overwrites any existing data in the column that it affects. A WRITETEXT statement cannot be used on text, ntext, and image columns in views.
• Active Data Object (ADO) applications can use the AppendChunk method to specify long amounts of ntext, text, or image data.
• OLE DB applications can use the ISequentialStream interface to write new ntext, text, or image values.
• Open Database Connectivity (ODBC) applications can use the data-at-execution form of SQLPutData to write new ntext, text, or image values.
• DB-Library applications can use the Dbwritetext function.

Using Bulk Copy Operations to Add Data

The bulk copy components of SQL Server enable you to insert large numbers of rows into a table or view and to retrieve large numbers of rows from a table, view, or query. Bulk copy is the fastest way to add large numbers of rows in SQL Server. Bulk copy operations are discussed in more detail in Chapter 7, "Managing and Manipulating Data."

Modifying Data in a SQL Server Database

After the tables have been created and the data has been added, changing or updating data in the tables becomes one of the day-to-day processes of maintaining a database. SQL Server provides several methods for changing data in an existing table:

• The UPDATE statement
• Database APIs and cursors
• The UPDATETEXT statement

Updates work on views as well as on tables (with some restrictions).

Using an UPDATE Statement to Modify Data

The UPDATE statement can change data values in single rows, in groups of rows, or in all of the rows in a table or view. You can also use this statement to update rows in a remote server by using either a linked server name or the OPENROWSET, OPENDATASOURCE, and OPENQUERY functions (as long as the OLE DB provider used to access the remote server supports updates). An UPDATE statement referencing a table or a view can change the data in only one base table at a time.

NOTE

---

An update is successful only if the new value is compatible with the data type of the target column and adheres to all constraints that apply to the column.

The UPDATE statement has these major clauses:

• SET
• WHERE
• FROM

Using a SET Clause to Modify Data

SET specifies the columns to be changed and the new values for the columns. The values in the specified columns are updated with the values given in the SET clause in all rows that match the WHERE clause search condition. If no WHERE clause is specified, all rows are updated.

For example, the following UPDATE statement includes a SET clause that increases the prices of the books in the NewBooks table by 10 percent:

 USE Pubs UPDATE NewBooks SET Price = Price * 1.1 

In this statement, no WHERE clause is used, so all rows in the table will be updated (unless the Price column contains a null value).

Using a WHERE Clause to Modify Data

The WHERE clause performs two functions:

• Specifies the rows to be updated
• Indicates the rows from the source tables that qualify to supply values for the update if a FROM clause is also specified

If no WHERE clause is specified, all rows in the table are updated.

In the following UPDATE statement, the WHERE clause is used to limit the update to only those rows that meet the condition defined in the clause:

 USE Pubs UPDATE NewBooks SET BookType = 'popular' WHERE BookType = 'popular_comp' 

This statement changes the name popular_comp to popular. If the WHERE clause was not included, all BookType values would be changed to popular.

Using a FROM Clause to Modify Data

You can use the FROM clause to pull data from one or more tables or views into the table that you want to update. For example, in the following UPDATE statement, the FROM clause includes an inner join that joins the titles in the NewBooks and Titles tables:

 USE Pubs UPDATE NewBooks SET Price = Titles.Price FROM NewBooks JOIN Titles      ON NewBooks.BookTitle = Titles.Title 

In this statement, the Price values in the NewBooks table are being updated to the same values that are in the Price column of the Titles table.

Using APIs and Cursors to Modify Data

The ADO, OLE DB, and ODBC APIs support updating the current row on which the application is positioned in a result set. In addition, when using a Transact-SQL server cursor, you can update the current row by using an UPDATE statement that includes a WHERE CURRENT OF clause. Changes made with this clause affect only the row on which the cursor is positioned. Cursors are discussed in more detail in Chapter 7, "Managing and Manipulating Data."

Modifying ntext, text, or image Data

SQL Server provides a number of methods that enable you to update ntext, text, or image values in a row when replacing the entire value:

• You can specify relatively short amounts of data in an UPDATE statement in the same way that you update char, nchar, or binary data.
• You can use the Transact-SQL WRITETEXT or UPDATETEXT statements to update ntext, text, or image values.
• ADO applications can use the AppendChunk method to specify long amounts of ntext, text, or image data.
• OLE DB applications can use the ISequentialStream interface to write new ntext, text, or image values.
• ODBC applications can use the data-at-execution form of SQLPutData to write new ntext, text, or image values.
• DB-Library applications can use the Dbwritetext function.

SQL Server also supports updating only a portion of an ntext, text, or image value. In DB-Library, this procedure can be done using the Dbupdatetext function. All other applications and Transact-SQL scripts, batches, stored procedures, and triggers can use the UPDATETEXT statement to update only a portion of an ntext, text, or image column.

Deleting Data from a SQL Server Database

SQL Server supports several methods that you can use to delete data in an existing table:

• The DELETE statement
• APIs and cursors
• The TRUNCATE TABLE statement

The data-modification statements work on views as well as on tables (with some restrictions).

Using a DELETE Statement to Delete Data

A DELETE statement removes one or more rows in a table or a view. The following syntax is a simplified form of a DELETE statement:

DELETE table_or_view FROM table_source WHERE search_condition

The table_or_view placeholder names a table or a view from which the rows are to be deleted. All rows in a table or view that meet the qualifications of the WHERE search condition are deleted. If a WHERE clause is not specified, all of the rows in a table or view are deleted. The FROM clause specifies additional tables or views and join conditions that can be used by the predicates in the WHERE clause search condition to qualify the rows to be deleted from a table or view. Rows are not deleted from the tables named in the FROM clause, only from the tables named in the DELETE clause.

Any table that has had all rows removed remains in the database. The DELETE statement deletes only rows from the table. The table must be removed from the database by using the DROP TABLE statement.

Consider the following DELETE statement:

 USE Pubs DELETE NewBooks FROM Titles WHERE NewBooks.BookTitle = Titles.Title      AND Titles.Royalty = 10 

In this statement, rows are deleted from the NewBooks table if the royalty on those books is 10 percent. The royalty is based on the values in the Royalty column of the Titles table.

Using APIs and Cursors to Delete Data

The ADO, OLE DB, and ODBC APIs support deleting the current row on which an application is positioned in a result set. In addition, Transact-SQL scripts, stored procedures, and triggers can use the WHERE CURRENT OF clause on a DELETE statement to delete the cursor row on which they are currently positioned. Cursors are discussed in more detail in Chapter 7, "Managing and Manipulating Data."

Using the TRUNCATE TABLE Statement to Delete Data

The TRUNCATE TABLE statement is a fast, non-logged method of deleting all rows in a table. This method is almost always faster than a DELETE statement with no conditions, because DELETE logs each row deletion and TRUNCATE TABLE logs only the deallocation of whole data pages. The TRUNCATE TABLE statement immediately frees all of the space occupied by that table's data and indexes. The distribution pages for all indexes are also freed.

The following TRUNCATE TABLE statement deletes all rows from the NewBooks table in the Pubs database:

 USE Pubs TRUNCATE TABLE NewBooks 

As with a DELETE statement, the table definition remains in the database after you have used the TRUNCATE TABLE statement (along with its indexes and other associated objects). The DROP TABLE statement must be used to drop the definition of the table.

Exercise 3:  Modifying Data in a SQL Server Database

In this exercise, you will create a table in the BookShopDB database and add data to that table. You will modify the data that you inserted into the table and then delete that data. When you have finished modifying and deleting the data, you will remove the table from the database. To complete this exercise, you should be logged into your Windows 2000 Server computer as Administrator.

To create a test table in the BookShopDB database

1. Open SQL Query Analyzer and connect to your local server.
2. In the Editor pane of the Query window, enter the following Transact-SQL code:

 USE BookShopDB CREATE TABLE Test1       (       RowID INT IDENTITY(1,1) NOT NULL,       Title VARCHAR(80) NOT NULL,       Type CHAR(12) NOT NULL DEFAULT ('Unknown'),       City VARCHAR(50) NULL,       Cost MONEY NULL       ) 

In this statement, you are creating a table named Test1. The table contains five columns.

3. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been completed successfully.

To use an INSERT...VALUES statement to add data to the Test1 table

1. In the Editor pane of the Query window, enter the following Transact-SQL code:

 INSERT INTO Test1 (Title, Type, Cost) VALUES ('Test Title', 'business', 27.00) 

In this statement, you are inserting a row into the Test1 table. The row includes values for the Title, Type, and Cost columns.

2. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that one row is affected.

3. Write a SELECT statement that enables you to view all of the data in the Test1 table.

What statement should you use?

4. Execute the SELECT statement.

The contents of the Test1 table appear in the Grids tab of the Results pane.

5. Review the contents of the Test1 table.

Notice that the table contains only one row—the one that you added by using the INSERT statement. SQL Server automatically generated the value in the RowID column. The value in the City column is null because no value was defined for that column.

To use an INSERT...SELECT statement to add data to the Test1 table

1. In the Editor pane of the Query window, enter the following Transact-SQL code:

 INSERT INTO Test1 (Title, Type, Cost)       SELECT Title, Type, Price       FROM Pubs.dbo.Titles 

In this statement, you are taking data from the Titles table in the Pubs database and inserting that data into the Test1 table.

2. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating the number of rows that have been affected.

3. Use a SELECT statement to view the data in the Test1 table.

Notice that the RowID values have been generated automatically and that each row in the City column contains a null value.

To use an UPDATE statement to modify data

1. View the data in the Test1 table.

If the Results pane still shows the query result from the last procedure, you can refer to that result. Otherwise, use a SELECT statement to view the table's contents.

2. Make a note of several books that have a business value in the Type column, and note the price of those books. You will need to refer to these notes as you modify data in the table.
3. In the Editor pane of the Query window, enter the following Transact-SQL code:

 UPDATE Test1 SET Cost = Cost * 2 WHERE Type = 'business' 

In this statement, you are increasing the value in the Cost column to twice the original amount for business books.

4. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating the number of rows that have been affected.

5. Use a SELECT statement to view the data in the Test1 table.

Notice that the value in the Cost column has been doubled for each business book.

To use a DELETE statement to remove data from the Test1 table

1. In the Editor pane of the Query window, enter the following Transact-SQL code:

 DELETE Test1 WHERE Title = 'Test Title' 

In this statement, you are deleting any rows from the table that contain a value of Test Title in the Title column.

2. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating the number of rows that have been affected.

3. Use a SELECT statement to view the data in the Test1 table.

Notice that the Test Title row has been removed from the table.

4. In the Editor pane of the Query window, enter the following Transact-SQL code:

 DELETE Test1 

In this statement, you are deleting all rows from the Test1 table.

5. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating the number of rows that have been affected.

6. Use a SELECT statement to view the data in the Test1 table.

Notice that the table now contains no data.

To use a DROP TABLE statement to remove the Test1 from the database

1. In the Editor pane of the Query window, enter the following Transact-SQL code:

 DROP TABLE Test1 

In this statement, you are removing the Test1 table from the BookShopDB database.

2. Execute the Transact-SQL statement.

A message appears in the Messages tab of the Results pane, stating that the command has been completed successfully.

3. Use the Object Browser window to ensure that the Test1 table has been removed from the database.
4. Close SQL Query Analyzer.

Lesson Summary

SQL Server supports a number of methods for adding new rows of data to tables in a SQL Server database, for changing the data in existing rows, and for deleting rows. Methods for inserting data include the INSERT statement; the SELECT... INTO statement; the WRITETEXT statement and several database API options, which can be used to add ntext, text, or image data to a row; and the bulk copy component for inserting large numbers of rows. Methods that you can use to modify data include the UPDATE statement, database APIs and cursors, and the UPDATETEXT statement. You can delete data from a database by using DELETE statements, by deleting the current row in a cursor or result set, or by using the TRUNCATE TABLE statement.