Contents > Getting Started Building ColdFusion MX Applications > Lesson 6: Adding and Updating SQL Data > Completing the Trip Maintenance application > Writing code to save new trips to the database Adding data using SQL INSERT with cfquery approach

Adding data using SQL INSERT with cfquery approach

In previous lessons, you used the SQL SELECT statement to retrieve data and the SQL DELETE statement to delete data from the Trips table in the Compass Travel database. To add new trips to the database using SQL, you must understand the syntax of the SQL INSERT statement.

The SQL INSERT statement inserts new rows into a relational table. The format of the INSERT statement is as follows:

INSERT INTO table_name
VALUES (value1, value2,....)

The database table named Clients contains information about people in the following rows:

LastName	FirstName	Address	City
Tom	Jones	12 State St	Boston
Peter	Green	1 Broadway	New York

After the following SQL statement executes:

INSERT INTO Clients
VALUES ('Smith', 'Kaleigh', '14 Greenway', 'Windham')

The table contains the following rows:

LastName	FirstName	Address	City
Tom	Jones	12 State St	Boston
Peter	Green	1 Broadway	New York
Smith	Kaleigh	14 Greenway	Windham

Notice that the values inserted in the table were surrounded by single quotation marks. In SQL, you must surround any text or date values with single quotation marks but numeric values are not.

Alternatively, you can specify the columns for which you want to insert data. This approach lets you insert data to some columns while omitting others. The syntax for this approach is as follows:

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)

For example, the syntax to add Kaleigh Smith of Windham, with the address unknown, you use the named column approach:

INSERT INTO Clients (LastName, FirstName, City)
VALUES ('Smith', 'Kaleigh', 'Windham')

You used the cfquery tag to execute SQL from ColdFusion. The cfquery tag passes SQL statements to your data source. As described in Configuring Your Development Environment, a data source stores information about how to connect to an indicated data provider, such as a relational database management system. The data source you established in that chapter stored information on how to access the Compass Travel database. The data source name was "CompassTravel".

Contents > Getting Started Building ColdFusion MX Applications > Lesson 6: Adding and Updating SQL Data > Completing the Trip Maintenance application > Writing code to save new trips to the database Adding data using SQL INSERT with cfquery approach

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | Bug Reporting

Version 6.1

Comments are no longer accepted for ColdFusion MX 6.1. ColdFusion 8 is the current version.