Use the INSERT command to insert new rows into a table. You can insert a single row at a time, or several rows as a result of a query. You can list the columns in the target list in any order.
The system inserts a declared default or null value for any columns not present in the target list. The system rejects the new column if you insert a null into a column declared not null.
If the expression for each column is not of the correct data type, the system attempts automatic type coercion.
INSERT INTO <table> [ ( <col>[,<col>…] ) ]
{ DEFAULT VALUES | VALUES [ ( <expression>[,<expression>…] ) | SELECT <query> }
Input | Description |
---|---|
<table> | The name of the table into which rows are to be inserted. |
<col> | The name of a column in a table. |
DEFAULT VALUES | Fill all columns with nulls or with values you specified when you created the table by using default clauses. |
<expression> | An expression or value that is assigned to a column. |
<query> | A query. |
The INSERT command has the following outputs:
Output | Description |
---|---|
insert 0 <n> | The command was successful. The <n> represents the number of rows that were inserted. |
Error: Reload allow NULLs mismatch <table> <col> | The command attempted to insert data from the indicated column of an external table into a the target table. However, the null settings of the two columns do not match. They must be same for both tables. The insert operation failed. |
Error: Large table size limit on <part>, SPU <Default ¶ Font>. | The insert operation would cause the table to exceed its maximum size. This size is 64 GB, unless large table support is enabled. |
You must be the admin user, the table owner, the owner of the database or schema where the table is defined, or your account must have the Insert privilege for the table or the Table object class. You must also have the Select privilege for any table specified in a WHERE clause.
The following provides sample usage.
MYDB.SCH1(USER)=> INSERT INTO films VALUES
('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');
MYDB.SCH1(USER)=> INSERT INTO films (code, title, did, date_prod,
kind) VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');
MYDB.SCH1(USER)=> INSERT INTO distributors (name) VALUES
('British Lion');
Because only the column name
is specified, the omitted column is assigned its default value.MYDB.SCH1(USER)=> INSERT INTO films SELECT * FROM tmp;