Single Rows

In its simplest form, the INSERT statement creates one new row from a list of column values and puts that row in the table. The following statement shows how to add a row to the stock table:

INSERT INTO stock
   VALUES (115, 'PRC', 'tire pump', 108, 'box', '6/box')

The stock table has the following columns:

• stock_num (a number that identifies the type of merchandise)
• manu_code (a foreign key to the manufact table)
• description (a description of the merchandise)
• unit_price (the unit price of the merchandise)
• unit (of measure)
• unit_descr (characterizes the unit of measure)

The values that are listed in the VALUES clause in the preceding example have a one-to-one correspondence with the columns of the stock table. To write a VALUES clause, you must know the columns of the tables as well as their sequence from first to last.

Possible Column Values

The VALUES clause accepts only constant values, not expressions. You can supply the following values:

• Literal numbers
• Literal DATETIME values
• Literal INTERVAL values
• Quoted strings of characters
• The word NULL for a NULL value
• The word TODAY for the current date
• The word CURRENT for the current date and time
• The word USER for your user name
• The word DBSERVERNAME (or SITENAME) for the name of the computer where the database server is running

Restrictions on Column Values

Some columns of a table might not allow null values. If you attempt to insert NULL in such a column, the statement is rejected. Other columns in the table might not permit duplicate values. If you specify a value that is a duplicate of one that is already in such a column, the statement is rejected. Some columns might even restrict the possible column values allowed. Use data integrity constraints to restrict columns. For more information, see Data Integrity.

Do not specify the currency symbols for columns that contain money values. Just specify the numeric value of the amount.

The database server can convert between numeric and character data types. You can give a string of numeric characters (for example, '-0075.6') as the value of a numeric column. The database server converts the numeric string to a number. An error occurs only if the string does not represent a number.

You can specify a number or a date as the value for a character column. The database server converts that value to a character string. For example, if you specify TODAY as the value for a character column, a character string that represents the current date is used. (The DBDATE environment variable specifies the format that is used.)

Serial Data Types

A table can have only one column of the SERIAL data type and only one column of the SERIAL8 data type. The database server generates values for serial columns. When you insert values, specify the value zero for the serial column. The database server generates the next actual value in sequence. Serial columns do not allow NULL values.

You can specify a nonzero value for a serial column (as long as it does not duplicate any existing value in that column), and the database server uses the value. That nonzero value might set a new starting point for values that the database server generates. (The next value the database server generates for you is one greater than the maximum value in the column.)

Listing Specific Column Names

You do not have to specify values for every column. Instead, you can list the column names after the table name and then supply values for only those columns that you named. The following example shows a statement that inserts a new row into the stock table:

INSERT INTO stock (stock_num,  description,  unit_price,  manu_code)
   VALUES (115,  'tyre pump ',  114,  'SHM')

Only the data for the stock number, description, unit price, and manufacturer code is provided. The database server supplies the following values for the remaining columns:

• It generates a serial number for an unlisted serial column.
• It generates a default value for a column with a specific default associated with it.
• It generates a NULL value for any column that allows nulls but it does not specify a default value for any column that specifies NULL as the default value.

  You must list and supply values for all columns that do not specify a default value or do not permit NULL values.

You can list the columns in any order, as long as the values for those columns are listed in the same order. For information about how to designate null or default values for a column, see the IBM Informix: Database Design and Implementation Guide.

After the INSERT statement in the preceding example is executed, the following new row is inserted into the stock table:

stock_num manu_code  description  unit_price unit unit_descr

      115       SHM  tyre pump           114

Both unit and unit_descr are blank, which indicates that NULL values exist in those two columns. Because the unit column permits NULL values, the number of tire pumps that can be purchased for $114 is not known. Of course, if a default value of box were specified for this column, then box would be the unit of measure. In any case, when you insert values into specific columns of a table, pay attention to what data is needed for that row.