Adding Data


You will need to insert data into tables at some point, so let's look at data inserting using the INSERT statement.

NOTE

In this chapter, you will add, update, and delete rows from tables in the ows data source. The reason you delete any added rows is to ensure that any example code and screen shots later in the book actually look like the way they're supposed to.

Feel free to add more rows if you'd like, but if you don't clean up when you're finished, your screens will look different from the ones shown in the figures. This isn't a problem, just something to bear in mind.


Using the INSERT Statement

You use the INSERT statement to add data to a table. INSERT is usually made up of three parts:

  • The table into which you want to insert data, specified with the INTO keyword.

  • The column(s) into which you want to insert values. If you specify more than one item, each must be separated by a comma.

  • The values to insert, which are specified with the VALUES keyword.

The Directors table contains the list of movie directors working with or for Orange Whip Studios. Directors can't be assigned projects (associated with movies) if they aren't listed in this table, so any new directors must be added immediately.

See Appendix G, "Sample Application Data Files" for an explanation of each of the data files and their contents.


Now you're ready to add the new director. The following code contains the SQL INSERT statement:

 INSERT INTO Directors(FirstName, LastName) VALUES('Benjamin', 'FORTA') 

Enter this statement into the SQL Query field as seen in Figure 7.1. Feel free to replace my name with your own. When you're finished, click the Execute button to insert the new row. Assuming no problems occur, you should see a confirmation screen like the one in Figure 7.2.

Figure 7.1. Type the statement into the SQL Query field, then click Execute.


Figure 7.2. As INSERT statements don't return data, no results will be returned.


TIP

How can you tell if an INSERT succeeds or fails? Well, no news is good news. If no error is returned, the INSERT has succeeded. If an error has occurred, it will be displayed.


Understanding INSERT

Now that you've successfully inserted a row using the SQL INSERT statement, take a minute to look at the statement's syntax.

The first line of your statement reads:

 INSERT INTO Directors(FirstName, LastName) 

The text immediately following the INTO keyword is the name of the table into which the new row is being insertedin this case, the Directors table.

Next, the columns being added are specified. The columns are listed within parentheses, and since multiple columns are specified, they are separated by a comma. A row in the Directors table requires both a FirstName and a LastName, so the INSERT statement specifies both columns.

NOTE

When you insert a row into a table, you can provide values for as few or as many columns as you like. The only restriction is that any columns defined as NOT NULL columnsmeaning they can't be left emptymust have values specified. If you don't set a value for a NOT NULL column, the database driver returns an error message and the row is not inserted.


The next line reads:

 VALUES('Benjamin', 'FORTA') 

A value must be specified for every column listed whenever you insert a row. Values are passed to the VALUES keyword; all values are contained within parentheses, just like their column names. Two columns are specified, so two values are passed to the VALUES keyword.

NOTE

When inserting rows into a table, columns can be specified in any order. But be sure that the order of the values in the VALUES keyword exactly matches the order of the columns after the table name, or you'll insert the wrong data into the columns.


To verify that the new director was added to the table, retrieve the complete list of directors using the following SQL statement:

 SELECT * FROM Directors 

As explained in Chapter 6, SELECT * means select all columns. As you can see in Figure 7.3, the new row was added to the table. Make a note of the DirectorID, which you'll need later to update or delete this row.

Figure 7.3. You can use SELECT statements to verify that INSERT operations were successful.


NOTE

In the previous INSERT statement, no value was provided for the DirectorID column. So where did that value come from? The Directors table was set up to automatically assign primary key values every time a new row is inserted. This is a feature supported by many databasesAccess calls these AutoNumber columns, SQL Server uses the term Identity, and other databases have their own names. As a result, you don't have to worry about creating unique values because the database does that for you.


TIP

INSERT can insert only one row at a time, unless the data being inserted is being retrieved from another table. In that case, a special form of the INSERT statement (called INSERT SELECT) can be used to insert all retrieved rows in a single operation.




Macromedia Coldfusion MX 7 Web Application Construction Kit
Macromedia Coldfusion MX 7 Web Application Construction Kit
ISBN: 321223675
EAN: N/A
Year: 2006
Pages: 282

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net