Adobe ColdFusion 8

Inserting data into a database

You use SQL INSERT statement to write information to a database. A write adds a new row to a database table. The basic syntax of an INSERT statement is as follows:

INSERT INTO table_name(column_names) VALUES(value_list)

where:

  • column_names specifies a comma-separated list of columns.
  • value_list specifies a comma-separated list of values. The order of values has to correspond to the order that you specified column names.

Note: There are additional options to INSERT depending on your database. For a complete syntax description for INSERT, see the product documentation.

For example, the following SQL statement adds a new row to the employees table:

INSERT INTO employees(EmpID, LastName, Firstname) VALUES(51, 'Smith', 'John')

This statement creates a new row in the employees table and sets the values of the EmpID, LastName, and FirstName fields of the row. The remaining fields in the row are set to Null. Null means the field does not contain a value.

When you, or your database administrator, creates a table, you can set properties on the table and the columns of the table. One of the properties you can set for a column is whether the field supports Null values. If a field supports Nulls, you can omit the field from the INSERT statement. The database automatically sets the field to Null when you insert a new row.

However, if the field does not support Nulls, you must specify a value for the field as part of the INSERT statement; otherwise, the database issues an error.

The LastName and FirstName values in the query are contained within single-quotation marks. This is necessary because the table columns are defined to contain character strings. Numeric data does not require the quotation marks.