The INSERT Statement

The INSERT Statement

The INSERT statement inserts one or more rows into a table. INSERT always takes the name of the table into which data is to be inserted, as well as the appropriate columns and values. The columns are listed as comma delimited in parentheses after the table name, and the values for each column are listed comma delimited in parentheses after the VALUES keyword.

NOTE

Data cannot be inserted into more than one table in a single operation. The same is true for UPDATE and DELETE operations.

The following example inserts a new row into a products table:

 INSERT INTO products(product_name, product_price) VALUES('Super deluxe widget', 299) 

CAUTION

Unlike ColdFusion, SQL is not typeless. Strings must be enclosed within quotation marks, and numbers must not be.

You cannot use INSERT to insert multiple rows unless the data is the result of a SELECT operation (known as an INSERT SELECT). The following example inserts all the data from one table into another:

 INSERT INTO products(product_name, product_price) SELECT product_name, product_price FROM new_prods 

TIP

ColdFusion developers often use INSERT SELECT as a way to insert multiple user selections. They do so by passing the user selections (usually from form fields) to an IN clause in the SELECT statement so that it selects only the user-selected data.