Executive Summary:
Writing T-SQL queries in SQL Server 2005's Query Editor or SQL Server 2000's Query Analyzer can help you accomplish tasks quickly. However, some database administrators and SQL Server administrators might shy away from using T-SQL queries because they look intimidating. Fortunately, writing T-SQL queries isn't that hard if you break down the queries into their components. This T-SQL lesson explores how to use the INSERT, UPDATE, and DELETE components to create simple queries that modify data in SQL Server 2005 and SQL Server 2000 database tables. |
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 
