SQL Server Magazine

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.

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 www.sqlmag.com, entering 98105 in the InstantDoc ID text box, and clicking the 98105 .zip hotlink. 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.

Continued on page 2

   Prev. page   [1] 2     next page