INSERT
Inserts
new rows into a table.
SYNTAX
| table_name |
Name
of the table you want to insert a new row into. |
| column_name |
Name
of the column you want to insert a value for. |
| constant |
Constant
value you want to insert. |
| bind_variable |
Name
of the bound variable you want to insert (ODBC only). |
DESCRIPTION
The INSERT
command inserts new rows into a table. You cannot insert rows into the
system catalog tables. To execute the INSERT command on a table you must
be the table owner, have DBA or SYSADM security privileges, or have INSERT
privilege for the entire table or for the specific column you want to
insert data into.
You can
use this command to insert a single row by providing values using the
VALUES keyword. The values provided may be constants, the results of built-in
functions, or bound variables (in a program using the ODBC API). You can
also use this command to insert a set of rows using data selected from
other tables using a SELECT statement. The rows you select must have columns
with data types that are compatible with the data types in the table you
are inserting them into.
When you
specify individual columns you want to provide values for, you may name
the columns in any order when you execute the INSERT command. Omitting
the column list implicitly specifies you want to use all columns, in the
order they were created in the table. In this case you must provide a
value for each column in the table, even if the value is empty. If the
values you provide to not match the data type of the column, DBMaker will
convert the values to the proper data type whenever possible. The default
value of a column is used if you do not provide a value for that column.
When you
insert data into a child table that has a foreign key linking it to a
parent table, you must obey referential integrity rules. This means you
cannot insert a value into a child key that does not exist in the parent
key unless it is a NULL value. You must insert a new row into the parent
key first.
To insert
a string that contains a single quote, you must replace the single quote
in the string with two consecutive single quotes. You must have an even
number of single quotes in a value, or DBMaker will wait for another single
quote to close the string value. If you want to insert the default value
in a row, you can leave the value empty or explicitly specify you want
to insert the default value using the DEFAULT keyword.
EXAMPLE
The following
example inserts a complete row into the Employees table.
INSERT INTO Employees VALUES (1234, 'John', '01/01/1998', 2500)
|
The following
example inserts values only into the EmpNo, Name, and HireDate columns.
INSERT INTO Employees (EmpNo, Name, HireDate)
VALUES (1234, 'John', '01/01/1998')
|
The following
example inserts rows into the Employees table that were selected from
the TempStaff table where the EmpNo column has values greater than 10567.
INSERT INTO Employees (EmpNo, Name, HireDate)
SELECT EmpNo, Name, HireDate FROM TempStaff WHERE EmpNo > 10567
|
The following
example inserts a row where the value being inserted into a CHAR column
contains a single quote, and the value being inserted into all other columns
is being set to the default value using the DEFAULT keyword.
INSERT INTO T1 VALUES ('Joe''s Diner', DEFAULT, DEFAULT)
|
RELATED FUNCTIONS
<
GRANT (Security Privileges) | Contents
| KILL CONNECTION >
|
