INSERT

Description

Inserts new rows into the specified table/view that will contain either the explicitly specified values or the values returned by the query expression.

Syntax

INSERT INTO [owner_name.] { table_name | view_name }

          [ (column_name, column_name, … ) ]

          { VALUES (value, value, … ) | query_expression };

Notes

• If the optional list of column names is specified, then only the values for those columns need be supplied. The rest of the columns of the inserted row will contain NULL values, provided the table definition allows NULL values and there is no DEFAULT clause for the columns. If a DEFAULT clause is specified for a column and the column name is not present in the optional column list, then the column takes the default value.
• If the optional list is not specified then all the column values have to be either explicitly specified or returned by the query expression. The order of the values should be the same as the order in which the columns have been declared in the declaration of the table/view.
• Explicit specification of the column values provides for insertion of only one row at a time. The query expression option allows for insertion of multiple rows at a time.
• If the table contains a foreign key, and there does not exist a corresponding primary key that matches the values of the foreign key in the record being inserted, the insert operation is rejected.
• You can use INSERT statements with query expressions to transfer rows from one remote table to another.

Examples

INSERT INTO customer (cust_no, name, street, city, state)

       VALUES

         (1001, 'RALPH', '#10 Columbia Street', 'New York', 'NY') ;

     INSERT INTO neworders (order_no, product, qty)

          SELECT order_no, product, qty

          FROM orders

          WHERE order_date = SYSDATE ;

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege.
• Ownership of the table.
• INSERT privilege on the table.

If a query_expression is specified, then the user must have any of the following privileges:

• DBA privilege.
• SELECT privilege on all the tables/views referred to in the query_expression.

  SQL Compliance	SQL-92, ODBC Core SQL grammar
  Environment	Embedded SQL, interactive SQL, ODBC applications
  Related Statements	Query Expressions