Page 504 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

The Insert Statement

The Insert statement adds a new row to a table. Here is its syntax:
Insert [Into] <table> [(<column> [, <column> ]…)]
[Values (<value> [, <value> ]…)]
Where <table> is the name of the table where the new row will be added,<column> is the
name of a column in the table, and <value> is the value for the corresponding column.

Note Like the Select statement, the syntax presented here covers only part of the full
syntax for the Insert statement. However, the information presented here will work on nearly
any database server. Check your database vendor’s documentation for the complete set of
options for the Insert statement.

Using the Insert Statement
You must specify the name of the table where the row will be added with the Insert statement;
following the table name is an optional list of column names and a list of values to be inserted
into the database. The position of each value in the list of values corresponds to the position
of the column in the list of columns. If you don’t specify all the columns in the Insert state­
ment, columns not listed will be set to Null.
Although the list of column names is optional, you probably should list them anyway.
Otherwise, you have to ensure that your values are listed in the same order that the columns
are listed in the database. Although this isn’t hard to verify, if you do make a mistake or, more
importantly, if the database is changed, the order of the columns might change.

Caution If your table contains an Identity column, you should not include it in the list of
Chapter 22
columns when you execute an Insert statement. The database will generally return an error
if you explicitly attempt to insert a value into the Identity column. There are techniques that
might permit you to insert a value instead of relying on the database to automatically gen€
erate one. See your database system’s documentation for more information.

The following statement shows how to insert a new row of data into the Customers table. The
value 12345 is stored in the CustomerId column, a customer is stored in the Name column,
and so forth. Any columns not specified in this list will contain Null.

Insert Into Customers (CustomerId, Name, Street, City, State, ZipCode)
Values (12345, ’A customer’, ’on a street’, ’Tacoma, ’WA’, 98422)

478
Part 6: Excel and the Outside World: Collaborating Made Easy