INSERT

SQL Guide : solidDB® SQL statements : INSERT

INSERT

INSERT INTO table_name insert_columns_and_source

insert_columns_and_source::=
from_subquery |
from_constructor |
from_default

from subquery ::=
[insert_column_name_list] query expression

insert_column_name_list ::=
([column name [, column name]... ])

from constructor ::=
[insert_column_name_list] VALUES
row_constructor[, row_constructor]... ]

row_constructor ::= ([insert_item[, insert_item]...])

insert_item ::= insert_value | DEFAULT | NULL

from default ::= DEFAULT VALUES

Usage

The INSERT statement inserts rows into a table.

There are several variations of the INSERT statement. In the simplest instance, a value is provided for each column of the new row in the order specified at the time the table was defined (or altered). In the preferable form of the INSERT statement, the columns are specified as part of the statement and they do not need to be in any specific order as long as the orders of the column list matches the order of the value list.

insert_value can be a literal, a scalar function, or a variable in a procedure.

Examples

INSERT INTO TEST (C, ID) VALUES (0.22, 5); INSERT INTO TEST VALUES (0.35, 9);

Multirow inserts can also be done. For example, to insert three rows in one statement, you can use the following command:

INSERT INTO employees VALUES (10021, 'Peter', 'Humlaut'), (10543, 'John', 'Wilson'), (10556, 'Bunba', 'Olo');

You can insert default values by using the DEFAULT VALUES statement as shown in the second example below. An equivalent form is “INSERT INTO TEST() VALUES()”. You can also assign a specific value for one column and use the default value for another column. These methods as shown in the examples below:

INSERT INTO TEST () VALUES ();

INSERT INTO TEST DEFAULT VALUES;

INSERT INTO TEST (C, ID) VALUES (0.35, DEFAULT);

INSERT INTO TEST (C, ID) SELECT A, B FROM INPUT_TO_TEST;