An important part of many DBAs’ and SQL Server administrators’ jobs is keeping databases current. Doing so involves adding, changing, and deleting data from tables. T-SQL makes these tasks easy. You just need to use INSERT, UPDATE, and DELETE statements. Before I show you how to use these statements, there are a few things you need to do before proceeding.
The Prerequisites
The sample INSERT, UPDATE, and DELETE statements I use in this article are run against a simple database named MyDB, which contains a few dummy tables and a table named MyTable. MyTable is used to store the names of the various dummy tables. The only purpose of the dummy tables is to take up space. By merely existing, they ensure that you’ll have data in the sysobjects table that you can use as a data source for inserting data into MyTable.
Related: T-SQL 101, Lesson 1
Assuming that you have the permissions needed to create databases and tables and to issue datamodification statements, you can create MyDB, MyTable, and the dummy tables with the MyDB .sql and MyTable.sql files. You can download these files by going to Download the Code hotlink icon above. After you’ve downloaded and unzipped the 98105.zip file, open a query window in either SQL Server 2005’s SQL Server Management Studio (SSMS) or SQL Server 2000’s Query Analyzer and copy the code in MyDB.sql into the window. In the two FILENAME entries, replace C:\Program Files Microsoft SQL Server\MSSQL.1\MSSQL\DATA MyDB.mdf with a valid path on your server. Run the query to create MyDB. To create MyTable and the dummy tables, copy the code in MyTable.sql into the query window and execute it.
Before working with data in any table, you should familiarize yourself with the table’s layout. So, open MyTable and do so.
Inserting a Single Record
A basic INSERT statement that adds a single record (aka row) to a table consists of three parts and looks like
INSERT INTO Part1 (Part2) VALUES (Part3)
Part1 is where you specify the target table that will hold the new record. Part2 is where you list the names of the columns in the target table for which you have data. You need to enclose the list in parentheses and use commas to separate column names. Part3 is where you provide the data to be inserted. When you want to specify the actual data values, you use the VALUES parameter followed by those values. You need to enclose the list of data values in parentheses and separate the values with commas. When a value is character based—such as a string or a datetime value—you also need to enclose it in single quotes (' '). For example, running the following statement inserts the data values of TestTable and 2007-09-22 into MyTable’s ObjectName and Creation- Date columns, respectively:
INSERT INTO MyTable
(ObjectName, CreationDate)
VALUES ('TestTable', '2007-09-22')
If you familiarized yourself with MyTable earlier, you probably noticed that I only specified two of the three columns in this INSERT statement. The first column is an identity column, which has a special meaning. The data for that column is automatically added and its values incremented with each record being inserted. This is a powerful feature of SQL Server that you should be aware of. If you want to learn more about identity data types, take a look at the IDENTITY (Property) Web page in SQL Server Books Online (BOL) at msdn2.microsoft.com/en-us/library/ms186775.aspx.
To view the newly inserted record, execute the code
SELECT * FROM MyTable
Figure 1 shows what the results should look like.
Inserting the Results from a SELECT Statement
In “T-SQL 101, Lesson 1” (March 2008, InstantDoc ID 97724), I showed you how to get data out of a database with a SELECT statement. You can use the INSERT statement to store the data retrieved by the SELECT statement in a table. Like the single-record INSERT statement, the INSERT statement that stores the results of a SELECT statement has three parts: the target database (Part1), the column names (Part2), and the data (Part3). However, instead of using the VALUES parameter to specify the actual data values in Part3, you use a SELECT statement that retrieves the data from another source. For example, when run against the MyDB database, the query
INSERT INTO MyTable (ObjectName, CreationDate) SELECT name, crdate FROM sysobjects WHERE type = 'U' ORDER BY name
inserts into our new table MyTable records that contain the name and creation date of all the user-defined tables in MyDB, ordered alphabetically by their names. So, assuming you ran the single-record INSERT statement previously discussed, your results from this query should look like those in Figure 2, with two exceptions. First, the CreationDate values for the MyTable and dummy tables will contain the date and time when you ran MyTable.sql. Second, the ObjectName column will be wider. (I shortened it for space reasons.)
In Figure 2, note that TestTable is still the first record. The ORDER BY name clause only applies to the new records that the SELECT statement is inserting. TestTable was added previously with the single-record INSERT statement.
You can use any SELECT statement you like, as long as the data types of the columns listed in Part2 of the INSERT statement match those columns specified in the SELECT statement. This opens the door for capturing all sorts of data. Use your newfound knowledge with caution, however. You don’t want to be inserting millions of records at a time on a busy server or on a server with limited disk space.
Updating Data
Now that you have a handle on inserting data, let’s look at how the UPDATE statement works. A simple UPDATE statement typically consists of three parts:
UPDATE Part1 SET Part2 WHERE Part3
Part1 is where you specify the target table. Part2 is where you specify the columns that are to be changed, along with the new data for each column. Part3 is optional but in most cases essential. This is where you specify a filter using the WHERE clause. If you don’t specify a WHERE clause, you’ll update every single record in the table. For example, the query
UPDATE MyTable SET CreationDate = '2007-09-23'
updates the CreationDate value for each record in MyTable, as Figure 3 shows. In Figure 3, note that the CreationDate value is 2007-09-23 00:00:00.000 and not 2007-09-23 as specified in the query. Because the data type of the CreationDate column is defined as datetime and the query doesn’t specify a time portion, SQL Server assumes you mean midnight and adds 00:00:00.000.
Now let’s use a WHERE clause to modify the ObjectName and CreationDate columns for a specific record:
UPDATE MyTable SET ObjectName = 'PartyTime', CreationDate = '1999-12-31 23:00:00’ WHERE TableID = 1
As Figure 4 shows, only the first record (specified by WHERE TableID = 1) is updated with the new table name of PartyTime and a new creation date of 1999-12-31 23:00:00.
Deleting Data
The command that every DBA fears will fall into the wrong hands is the DELETE statement. Although it can be misused, it serves an important role when using queries to modify data in tables. A basic DELETE statement typically consists of two parts:
DELETE Part1 WHERE Part2
Part1 is where you specify the target table. Part2 is where you specify a filter using the WHERE clause. Like the WHERE clause in an UPDATE statement, the WHERE clause in a DELETE statement is optional but usually essential. If you don’t include a WHERE clause and filter, you’ll delete all the records in the specified table.
Let’s say you want to delete the records with a creation date older than September 22, 2007 from MyTables. The query
DELETE MyTable WHERE CreationDate > '2007-09-22'
will do just that, as Figure 5 shows. You need to be careful when modifying data with either the DELETE or UPDATE statement. Always include a WHERE clause unless you’re positive you want to affect all the records in a table. If you’re unsure of how many records you’ll be affecting, try running a SELECT statement with the same WHERE clause.
Instead of specifying columns in the SELECT statement, you can specify COUNT (*), which will return a count of the rows that would be affected. For example, to check the effect of the last DELETE statement, you could run
SELECT COUNT (*) FROM MyTable WHERE CreationDate > '2007-09-22'
If the result set is in the millions, chances are you need to refine your WHERE clause. I’ll cover the use of the COUNT function in the next lesson, so don’t fret if you can’t get it to work.
Explore on Your Own
Once you’re comfortable in modifying the data in MyTable, you might try running INSERT, UPDATE, and DELETE statements against tables in an existing database on a nonproduction server. To do so, copy the code in ExistingDatabaseQuery. sql (which is in the 98105 .zip file) in your query window, replace MyDB in the first line to the name of your existing database, and run the query. Afterward, you’ll be able to customize the sample INSERT, UPDATE, and DELETE statements to your environment and run them. By doing so, you’ll learn how to better use these statements. You might even find data that you didn’t know existed.
