INSERT Statement

The INSERT statement is used to add records to a database table. Its general format for adding a single record is shown below:

INSERT INTO TableName
     [(FieldName1 [,FieldName2]...)]
     VALUES (Value1 [,Value2]...)

The keywords INSERT INTO are followed by the name of the table into which a new record is to be added. Optionally, the table name is followed by a comma-separated list of field names, enclosed in parentheses, for which values are supplied. The VALUES clause gives, in parentheses, a comma-separated list of corresponding data values for the added fields. No set of records is returned by an INSERT statement.

Typically, all fields are named and supplied with values, but this need not be the case. Those fields which are named and which have corresponding values are entered into the added record; unspecified fields are added but have no values assigned. If no field names are supplied, any values are assigned beginning with the first field.

The most common and clearest way to specify a new record is to name all fields and to supply a value for each:

INSERT INTO MyTable
  (Field1, Field2, Field3, Field4, Field5)
  VALUES('a text field value', 'a memo field value', 100, 3000, #01/01/04#)

In this example, a new record containing five fields is added to table MyTable. The names of the fields are provided along with the data values to be assigned, respectively, to each field. Values for fields that are defined as text fields in the table must be enclosed in single quotes (apostrophes); values for numeric fields are not enclosed in single quotes; values for date/time fields are enclosed in # symbols. It is perfectly acceptable, though, to insert a new record with only some of the fields having values, as long as the values match the field names and data types:

INSERT INTO MyTable
  (Field1, Field5)
  VALUES('a text field value', #01/01/04#)

If an inserted text field contains apostrophes, they must be replaced with double apostrophes. See the discussion about apostrophes in data values on the SELECT page.

Inserting Variables into INSERT Statements

As in the case of the SELECT statement, normally SQL INSERT statements are composed by a script which inserts variable data into the statements.


SQLString = "INSERT INTO Products (ItemNo, ItemName, ItemPrice, ItemQty, ItemDate) VALUES ('$ItemNo', '$ItemName', $ItemPrice,$ItemQuantity,#$PurchaseDate#)"

This construction produces an INSERT statement resembling the following:

SQLString = "INSERT INTO Products (ItemNo, ItemName, ItemPrice, ItemQty, ItemDate) 
VALUES ('AAA111', 'Software', 100.00, 15, #07/15/04#)"