Informix Guide to SQL: Tutorial Modifying Data

The INSERT statement adds a new row, or rows, to a table. The statement has two basic functions. It can create a single new row using column values you supply, or it can create a group of new rows using data selected from other tables.

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.

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

Some columns of a table might not allow null values. If you attempt to insert NULL in such a column, the statement is rejected, or a column 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.

Only one column in a table can have the SERIAL data type. The database server generates values for a serial column. To make this happen 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. However, 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.

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.)

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 nulls.

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 Informix Guide to Database Design and Implementation.

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 nulls, the number of tire pumps that were 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.

You can insert rows into a typed table in the same way you insert rows into a table not based on a row type.

When a typed table contains a row-type column (the named row type that defines the typed table contains a nested row type), you insert into the row-type column in the same way you insert into a row-type column for a table not based on a row type. The following section, Inserting into Row-Type Columns, describes how to perform inserts on row-type columns.

This section uses row types zip_t, address_t, and employee_t and typed table employee for examples. Figure 6-1 shows the SQL syntax that creates the row types and table.

CREATE ROW TYPE zip_t

(

	z_code			CHAR(5),

	z_suffix			CHAR(4)

);



CREATE ROW TYPE address_t

(

	street		VARCHAR(20),

	city		VARCHAR(20),

	state		CHAR(2),

	zip		zip_t

);



CREATE ROW TYPE employee_t 

(

name		VARCHAR(30),

address		address_t,

salary		INTEGER

);



CREATE TABLE employee OF TYPE employee_t;

The following syntax rules apply for inserts on columns that are defined on named row types or unnamed row types:

• Specify the ROW constructor before the field values to be inserted.
• Enclose the field values of the row type in parentheses.
• Cast the row expression to the appropriate named row type (for named row types).

The following statement shows you how to insert a row into the employee table in Figure 6-1 on page 6-15:

INSERT INTO employee
	VALUES ('Poole, John', 

	ROW('402 High St', 'Willits', 'CA', 
	ROW('69055','1450'))::address_t, 35000 )

Because the address column of the employee table is a named row type, you must use a cast operator and the name of the row type (address_t) to insert a value of type address_t.

Suppose you create the table that Figure 6-2 shows. The student table defines the s_address column as an unnamed row type.

CREATE TABLE student 
(
s_name			VARCHAR(30),
s_address			ROW(street VARCHAR (20), city VARCHAR(20),
				state CHAR(2), zip VARCHAR(9)),
				grade_point_avg DECIMAL(3,2)
);

The following statement shows you how to add a row to the student table. To insert into the unnamed row-type column s_address, use the ROW constructor but do not cast the row-type value.

INSERT INTO student
	VALUES (
		`Keene, Terry', 
		ROW('53 Terra Villa', 'Wheeling', 'IL', '45052'),

		3.75
		)

The fields of a row-type column can contain null values. You can specify null values either at the level of the column or the field.

The following statement specifies a null value at the column level to insert null values for all fields of the s_address column. When you insert a null value at the column level, do not include the ROW constructor.

INSERT INTO student
	VALUES ('Brauer, Howie', NULL, 3.75)

When you insert a null value for particular fields of a row type, you must include the ROW constructor. The following INSERT statement shows how you might insert null values into particular fields of the address column of the employee table. (The address column is defined as a named row type.)

INSERT INTO employee
	VALUES (
		'Singer, John',

		ROW(NULL, 'Davis', 'CA', ROW(97000, 
	 		2000))::address_t, 
		67000
		)

When you specify a null value for the field of a row type, you do not need to explicitly cast the null value when the row type occurs in an INSERT statement, an UPDATE statement, or a program variable assignment.

The following INSERT statement shows how you insert null values for the street and zip fields of the s_address column for the student table:

INSERT INTO student
	VALUES( 
		Henry, John',
		ROW(NULL, s_address.city, s_address.state, NULL)
		)

No special considerations exist when you insert a row into a supertable. An INSERT statement applies only to the table that is specified in the statement. For example, the following statement inserts values into the supertable but does not insert values into any subtables:

INSERT INTO person
	VALUES (
		'Poole, John', 
		ROW('402 Saphire St.', 'Elmondo', 'CA', 69055'),
		345605900
		)

This section describes how to insert a collection value into a column with DB-Access or the Relational Object Manager. It does not discuss how to insert individual elements into a collection column. To access or modify the individual elements of a collection, use an ESQL/C program or SPL routine. For information about how to create an ESQL/C program to insert into a collection, see the Informix ESQL/C Programmer's Manual. For information about how to create an SPL routine to insert into a collection, see Chapter 10.

The examples that this section provides are based on the manager table in Figure 6-3. The manager table contains both simple and nested collection types.

CREATE TABLE manager
(
	mgr_name				VARCHAR(30),
	department				VARCHAR(12),
	direct_reports				SET(VARCHAR(30) NOT NULL),
	projects				LIST(ROW(pro_name VARCHAR(15),
					pro_members SET(VARCHAR(20)
					NOT NULL) ) NOT NULL)
);

When you insert values into a row that contains a collection column, you insert the values of all the elements that the collection contains as well as values for the other columns. For example, the following statement inserts a single row into the manager table, which includes columns for both simple collections and nested collections:

INSERT INTO manager(mgr_name, department, direct_reports, 
projects)
VALUES 

(
'Sayles', 'marketing', 
"SET{'Simonian', 'Waters', 'Adams', 'Davis', 'Jones'}", 
"LIST{

	ROW('voyager_project', SET{'Simonian', 'Waters',
	'Adams', 'Davis'}), 

	ROW ('horizon_project', SET{'Freeman', 'Jacobs',
	'Walker', 'Smith', 'Cannan'}), 

	ROW ('saphire_project', SET{'Villers', 'Reeves',

	'Doyle', 'Strongin'})
	}"
)

To insert values into a collection that is a row type, you must specify a value for each field in the row type.

In general, null values are not allowed in a collection. However, if the element type of the collection is a row type, you can insert null values into individual fields of the row type.

You can also specify an empty collection. An empty collection is a collection that contains no elements. To specify an empty collection, use the braces ({}). For example, the following statement inserts data into a row in the manager table but specifies that the direct_reports and projects columns are empty collections:

INSERT INTO manager
	VALUES ('Sayles', 'marketing', "SET{}", 
	"LIST{ROW(SET{})}")

A collection column cannot contain null elements. The following statement returns an error because null values are specified as elements of collections:

INSERT INTO manager
	VALUES ('Cole', 'accounting', "SET{NULL}",
	"LIST{ROW(NULL, ""SET{NULL}"")}"

The following syntax rules apply for performing inserts and updates on collection types:

• Use braces ({}) to demarcate the elements that each collection contains
• If the collection is a nested collection, use braces ({}) to demarcate the elements of both the inner and outer collections

When you use the INSERT statement to insert an object into a BLOB or CLOB column, the database server stores the object in an sbspace rather than the table. The database server provides SQL functions that you can call from within an INSERT statement to import and export BLOB or CLOB data, otherwise known as smart large objects. For a description of these functions, see page 4-20.

The following INSERT statement uses the FILETOBLOB() and FILETOCLOB() functions to insert a row of the inmate table. (Figure 4-2 on page 4-20 defines the inmate table.)

INSERT INTO inmate
	VALUES (437, FILETOBLOB('datafile', 'client'), 
		FILETOCLOB('tmp/text', 'server'))

In the preceding example, the first argument for the FILETOBLOB() and FILETOCLOB() functions specifies the path of the source file to be copied into the BLOB and CLOB columns of the inmate table, respectively. The second argument for each function specifies whether the source file is located on the client computer ('client') or server computer ('server'). To specify the path of a filename in the function argument, apply the following rules:

• If the source file resides on the server computer, you must specify the full pathname to the file (not the pathname relative to the current working directory).
• If the source file resides on the client computer, you can specify either the full or relative pathname to the file.

The other major form of the INSERT statement replaces the VALUES clause with a SELECT statement. This feature allows you to insert the following data:

• Multiple rows with only one statement (each time the SELECT statement returns a row, a row is inserted)
• Calculated values (the VALUES clause permits only constants) because the select list can contain expressions

For example, suppose a follow-up call is required for every order that has been paid for but not shipped. The INSERT statement in the following example finds those orders and inserts a row in cust_calls for each order:

INSERT INTO cust_calls (customer_num, call_descr)

	SELECT customer_num, order_num FROM orders

		WHERE paid_date IS NOT NULL

		AND ship_date IS NULL

This SELECT statement returns two columns. The data from these columns (in each selected row) is inserted into the named columns of the cust_calls table. Then an order number (from order_num, a serial column) is inserted into the call description, which is a character column. Remember that the database server allows you to insert integer values into a character column. It automatically converts the serial number to a character string of decimal digits.

The following list contains the restrictions on the SELECT statement for inserting rows:

• It cannot contain an INTO clause.
• It cannot contain an INTO TEMP clause.
• It cannot contain an ORDER BY clause.
• It cannot refer to the table into which you are inserting rows.

Enterprise Decision Server allows you to use a SELECT statement that contains an ORDER BY clause in an INSERT SELECT statement.

The INTO, INTO TEMP, and ORDER BY clause restrictions are minor. The INTO clause is not useful in this context. (For more information, see Chapter 7.) To work around the INTO TEMP clause restriction, first select the data you want to insert into a temporary table and then insert the data from the temporary table with the INSERT statement. Likewise, the lack of an ORDER BY clause is not important. If you need to ensure that the new rows are physically ordered in the table, you can first select them into a temporary table and order it, and then insert from the temporary table. You can also apply a physical order to the table using a clustered index after all insertions are done.

Important: The last restriction is more serious because it prevents you from naming the same table in both the INTO clause of the INSERT statement and the FROM clause of the SELECT statement. Naming the same table in both the INTO clause of the INSERT statement and the FROM clause of the SELECT statement causes the database server to enter an endless loop in which each inserted row is reselected and reinserted.

In some cases, however, you might want to select from the same table into which you must insert data. For example, suppose that you have learned that the Nikolus company supplies the same products as the Anza company, but at half the price. You want to add rows to the stock table to reflect the difference between the two companies. Optimally, you want to select data from all the Anza stock rows and reinsert it with the Nikolus manufacturer code. However, you cannot select from the same table into which you are inserting.

To get around this restriction, select the data you want to insert into a temporary table. Then select from that temporary table in the INSERT statement, as the following example shows:

SELECT stock_num, 'NIK' temp_manu, description, unit_price/2

		half_price, unit, unit_descr FROM stock

	WHERE manu_code = 'ANZ'

		AND stock_num < 110

	INTO TEMP anzrows;



INSERT INTO stock SELECT * FROM anzrows;



DROP TABLE anzrows;  

This SELECT statement takes existing rows from stock and substitutes a literal value for the manufacturer code and a computed value for the unit price. These rows are then saved in a temporary table, anzrows, which is immediately inserted into the stock table.

When you insert multiple rows, a risk exists that one of the rows contains invalid data that might cause the database server to report an error. When such an error occurs, the statement terminates early. Even if no error occurs, a small risk exists that a hardware or software failure might occur while the statement is executing (for example, the disk might fill up).

In either event, you cannot easily tell how many new rows were inserted. If you repeat the statement in its entirety, you might create duplicate rows, or you might not. Because the database is in an unknown state, you cannot know what to do. The solution lies in using transactions, as Interrupted Modifications discusses.