INSERT

An INSERT statement creates a row or rows and stores them in the named table. The number of values assigned in an INSERT statement must be the same as the number of specified or implied columns.

Syntax

INSERT INTO table-name
    [ ( simple-column-name [ , simple-column-name ]* ) ]
   Query

Description

Query can be:
  • a VALUES list
  • a multiple-row VALUES expression
Note: SQLFire does not support an INSERT with a subselect query if any subselect query requires aggregation.

Single-row and multiple-row lists can include the keyword DEFAULT. Specifying DEFAULT for a column inserts the column's default value into the column. Another way to insert the default value into the column is to omit the column from the column list and only insert values into other columns in the table. More information is provided in VALUES Expression.

SELECT and JOIN Operations provide additional information.

The INSERT statement depends on the table being inserted into, all of the conglomerates (units of storage such as heaps or indexes) for that table, and any other table named in the statement. Any statement that creates or drops an index or a constraint for the target table of a prepared INSERT statement invalidates the prepared INSERT statement.

Example

INSERT INTO TRADE.CUSTOMERS
      VALUES (1, ''J Pearson', '07-06-2002', 'VMWare', 1)

-- Insert a new customer into the CUSTOMERS  table,
-- but do not assign  value to  'SINCE'  column
INSERT INTO TRADE.CUSTOMERS(CID ,CUST_NAME , ADDR ,TID)
 VALUES (1, 'J. Pearson', 'VMWare', 1)

-- Insert two new customers using one statement 
-- into the CUSTOMER table as in the previous example, 
-- but do not assign  value to 'SINCE'  field of the new customer.
INSERT INTO TRADE.CUSTOMERS (CID ,CUST_NAME , ADDR ,TID)
 VALUES (1, 'J. Pearson' , 'VMWare', 1),
 (2, 'David Y.' , 'VMWare', 1)

-- Insert the DEFAULT value for the LOCATION column
INSERT INTO TRADE.CUSTOMERS
      VALUES (1, 'J. Pearson', DEFAULT, 'VMWare',1)