Categories Software Related Stories SQL: The Universal Database Language -- 11/3/98 Next: The SELECT statement

Continued from Introduction

The INSERT statement allows you to insert rows into a named table. For example, to insert John Smith's employee record into the example table, enter the following SQL statement:

INSERT INTO EMPLOYEES
   VALUES ( 'Smith', 'John', '1980-06-10', 'Los Angeles', 16, 45000) ;

In this form of the INSERT statement, the system will attempt to enter the values into corresponding columns. The columns are in the order in which they were defined in the CREATE TABLE statement. In our example, the first value, 'Smith', goes into the first column, LAST_NAME; the second value, 'John', goes into the second column, FIRST_NAME; and so on.

We said the system will attempt to enter values; it does type checking as well as rule enforcement. If you violate a rule--by trying to enter a character string into a numeric column, for example--the system will reject the entry and return an error message.

If SQL rejects one of the column values you've entered, none of the other column values in the statement will be entered either. That's because SQL supports transactions. A transaction takes the database from one consistent state to another. If part of a transaction fails, then all of it fails; the system is restored, or rolled back, to the state it was in prior to the failed transaction.

Going back to our INSERT example, notice that all of the integer and decimal values were entered without quotation marks or formatting, while the character and date values were enclosed in single quotation marks. Inserting commas into numerals for readability will result in an error. (Remember that in SQL, commas are item separators.)

Also note that it's important to use single quotes when entering literal values. Double quotes are used to enclose delimited identifiers.

For date entry, we used the standard SQL date format--yyyy-mm-dd--but systems can be customized to accept other formats. Of course, with the year 2000 right around the corner, you'll want to use four digits for the year.

Now that you understand how the INSERT statement works, let's populate the rest of the EMPLOYEES table:

INSERT INTO EMPLOYEES
   VALUES ( 'Bunyan', 'Paul', '1970-  07-04', 'Boston', 12, 70000) ;
INSERT INTO EMPLOYEES
   VALUES ( 'John', 'Adams', '1992-01-  21', 'Boston', 20, 100000) ;
INSERT INTO EMPLOYEES
   VALUES ( 'Smith', 'Pocahontas',   '1976-04-06', 'Los Angeles', 12,   100000) ;
INSERT INTO EMPLOYEES
   VALUES ( 'Smith', 'Bessie', '1940-  05-02', 'Chicago', 5, 200000) ;
INSERT INTO EMPLOYEES
   VALUES ( 'Jones', 'Davy', '1970-10-  10', 'Boston', 8, 45000) ;
INSERT INTO EMPLOYEES
   VALUES ( 'Jones', 'Indiana', '1992-  02-01', 'Chicago', NULL, NULL) ;

In the last entry, we did not know the values for Mr. Jones's grade level or salary, so we entered NULL (without quotes). NULLs are a special case in SQL that we shall discuss in more detail later. For right now, however, we'll just say that a NULL indicates that the value is missing.

Sometimes, as in the case we just mentioned, you may wish to enter values for some of the columns and not all of them. Rather than entering NULL for the columns you want to omit, you can use another form of the INSERT statement, given below:

INSERT INTO EMPLOYEES ( FIRST_NAME,
   LAST_NAME, HIRE_DATE,BRANCH_OFFICE)
   VALUES ( 'Indiana', 'Jones', '1992-02-01', 'Indianapolis') ;

In this form, we've placed a list of column names after the table name. (Default values are automatically entered into the columns omitted from the list. If no defaults are defined, NULLs are entered.) Notice that we've changed the column ordering and that the value order corresponds to the new column order. If this statement had omitted FIRST_NAME or LAST_NAME (which are constrained to be NOT NULL), the SQL command would have failed.

To recap, let's look at the syntax diagram for the two forms of the INSERT statement you've just learned:

INSERT INTO table [ ( column {, column } ) ]
   VALUES ( column1value [ { , columnXvalue } ] ) ;

As in Part 1, we used square brackets ([ ]) to denote optional elements and curly braces ({ }) to denote an element that can be repeated any number of times. (You wouldn't include these special characters in the actual SQL statement.) The regular parentheses are required in the VALUES clause and the optional column list.

Next: The SELECT statement

Published as PC Tech Feature in the 11/17/98 issue of PC Magazine.

Elsewhere on ZDNet
• Learn SQL Online -- ZD University
• Business Software: Databases -- ZD Products