IBM PureData System for Analytics, Version 7.1

INSERT

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.

Syntax

Syntax for using the INSERT command:

INSERT INTO <table> [ ( <col>[,<col>…] ) ]
{ DEFAULT VALUES | VALUES [ ( <expression>[,<expression>…] ) | SELECT <query> }

Inputs

The INSERT command takes the following inputs:

Table 1. INSERT Input
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.

Outputs

The INSERT command has the following outputs:

Table 2. INSERT 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.

Privileges

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.

Usage

The following provides sample usage.

Insert a single row into the table films:

MYDB.SCH1(USER)=> INSERT INTO films VALUES
('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');

In this sample, the last column (len, which indicates the film length) is omitted, and therefore that column assumes the default value of null:
```
MYDB.SCH1(USER)=> INSERT INTO films (code, title, did, date_prod,
kind) VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');
```
Insert a single row into the table distributors:
```
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.

Insert all rows of the table tmp into the table films:

MYDB.SCH1(USER)=> INSERT INTO films SELECT * FROM tmp;