INSERT
INSERT#
Syntax#
insert ::=#
single_table_insert ::=#
returning_clause ::=, subquery ::=
table_clause ::=#
multi_table_insert ::=#
values_clause ::=#
wait_clause ::=#
Prerequisites#
The SYS user, the owner of the schema containing the table, users having the INSERT ANY TABLE system privilege, and users having the INSERT privilege for the specified table can insert rows into tables using this statement.
If the user wishes to insert a record into a view, the user must have the same privileges on the base tables as above.
Description#
The INSERT statement is used to insert a new record into the specified table or partition. If an index has been defined for the table, the index data will also be modified.
user_name#
This is used to specify the name of the owner of the table into which the record(s) are to be inserted. If omitted, Altibase will assume that the table belongs to the schema of the user connected via the current session.
tbl_name#
This is used to specify the name of the table into which the record(s) are to be inserted.
view_name#
This specifies the name of the view into which a record is to be inserted.
subquery#
This specifies the view into which a record is to be inserted as a subquery.
NULL#
When the values to be inserted are provided only for some columns, but not for others, NULL is inserted into every column for which an insert value is not provided and which does not have a set DEFAULT value. (The default value for a TIMESTAMP column is the system time at the time that the INSERT operation occurred. Therefore, if no insert value is provided for a TIMESTAMP column, the system time, rather than NULL, is inserted into that column.)
NULL values can also be inserted by explicitly specifying NULL in the VALUES clause
multi_table_insert Clause#
This clause is used to insert a row of the result set generated from a subquery into one or more tables. If an expression exists in the SELECT list of the subquery, the expression must be given an alias to enable it to be referenced from the VALUES clause.
DEFAULT#
If the DEFAULT keyword is specified in the VALUES clause, the previously set default value will be inserted into the corresponding column. To insert the default values for all columns, use the DEFAULT VALUES clause.
If the DEFAULT keyword is specified for a timestamp column, the system time will be inserted.
INSERT ~ SELECT#
This type of query is used to insert the results of execution of a SELECT query into a table. The table from which the results were retrieved can be the same table as the table into which the records are to be inserted. The number of columns to be inserted must be the same as the number of columns in the SELECT statement, and corresponding columns must have compatible data types.
returning_clause#
Please refer to the returning_clause of the DELETE statement.
wait_clause#
Refer to the FOR UPDATE clause of the SELECT statement for in-depth information on wait_clause.
HINTS Options#
Please refer to "Hint Syntax" and "Hint List" for more detailed information.
Precautions#
- When entering data with an INSERT statement, please keep the following points in mind:
- The number of columns that are specified must be the same as the number of values to be inserted, and the data types must be compatible.
- When a partition is specified, it is impossible to insert values that do not match the partition conditions.
- It is permissible to insert NULL values when no default value has been specified for a column, as long as the column does not have the NOT NULL constraint
-
INSERT can fail due to the CHECK constraint.
-
Direct-Path INSERT has the following limitations:
- The destination table must be a disk table, and it cannot have any LOB columns or indexes.
- The destination table can't be a replication target table.
- The destination table can't have any triggers or referential integrity constraints defined for it.
- The target table cannot have the CHECK constraint.
Examples#
Inserting Simple Data#
<Query> Inser customer information named "Louise Leroux".
INSERT INTO customers
VALUES ( '25'
, 'Leroux'
, 'Louise'
, 'student'
,'025282222'
, 'F'
, '0101'
, 150763
, '#3 825 - 17th Ave SW Calgary Canada');
<Query> Insert only the number, name, and gender of information of the customer "Rosalia Jung".
INSERT INTO employees(eno, e_firstname, e_lastname, sex)
VALUES ( 21
, 'Rosalia'
, 'Jung'
, 'F');
<Query> Inser multiple rows at once.
INSERT INTO goods
VALUES ('Y111100001', 'YY-300', 'AC0001', 1000, 78000)
, ('Y111100002', 'YY-310', 'DD0001', 100, 98000)
, ('Y111100003', 'YY-H5000', 'AC0002', 780, 35800);
Inserting Complicated Data#
<Query> Copy the customer number and order date for all delayed orders from the orders table to the delayed_processing table.
CREATE TABLE delayed_processing
(
cno CHAR(14),
order_date DATE
);
INSERT INTO delayed_processing
SELECT cno
, order_date
FROM orders
WHERE PROCESSING = 'D';
Inserting Partitioned Data#
CREATE TABLE T1 ( I1 INTEGER, I2 INTEGER )
PARTITION BY RANGE ( I1 )
(
PARTITION P1 VALUES LESS THAN ( 300 ),
PARTITION P2 VALUES LESS THAN ( 400 ),
PARTITION P3 VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
INSERT INTO T1 PARTITION ( P1 ) VALUES ( 123, 456 );
1 row inserted.
Inserting Data using the Direct-Path INSERT Hint#
<Query> Copy all data from table T1 to table T2 using the Direct-Path INSERT hint.
INSERT /*+ APPEND */ INTO T2 SELECT * FROM T1;
Multi-table INSERT statement#
<Query> Multiple rows are inserted into a single table at once.
CREATE TABLE t
(
pid INTEGER,
fname VARCHAR(20),
lname VARCHAR(25)
);
INSERT INTO t VALUES (1, 'Dan', 'Morgan'), (2, 'Jeremiah', 'Wilton'), (3, 'Helen', 'Lofstrom');
<Query> The result of a subquery is inserted into multiple tables.
CREATE TABLE sal_history
(
eno INTEGER,
join_date DATE,
salary NUMBER(10, 2)
);
CREATE TABLE dno_history
(
eno INTEGER,
dno SMALLINT,
chg_date DATE
);
INSERT ALL INTO sal_history VALUES(emp_id, join_date, salary)
INTO dno_history VALUES(emp_id, dept_id, sysdate)
SELECT eno EMP_ID
, join_date
, salary
, dno DEPT_ID
FROM employees;
Inserting Data using the RETURNING Clause#
<Query> The following example returns the input row values as output bind variables :v1, :v2.
CREATE TABLE employees
(
eno INTEGER,
ename VARCHAR(20)
);
VAR v1 OUTPUT INTEGER;
VAR v2 OUTPUT VARCHAR(30);
PREPARE INSERT INTO employees VALUES (1, 'jake') RETURN eno, ename INTO :v1, :v2;
1 row inserted.
PRINT var
[ HOST VARIABLE ]
-------------------------------------------------------
NAME TYPE VALUE
-------------------------------------------------------
V1 INTEGER 1
V2 VARCHAR(30) jake
Inserting Data into Join Views#
<Query> Create a view joining the two tables, employees and departments, and insert records.
CREATE VIEW simple_emp AS
SELECT eno
, e_lastname
, e_firstname
, emp.dno dno
FROM employees emp
, departments dept
WHERE emp.dno = dept.dno;
SELECT * FROM simple_emp;
ENO E_LASTNAME E_FIRSTNAME DNO
-------------------------------------------------------------------------
3 Kobain Ken 1001
16 Chen Wei-Wei 1001
.
.
.
20 Blake William 4002
19 rows selected.
INSERT INTO simple_emp(eno, e_lastname, e_firstname, dno) VALUES(50, 'Kim', 'Yong', 1001);
SELECT * FROM simple_emp;
ENO E_LASTNAME E_FIRSTNAME DNO
-------------------------------------------------------------------------
3 Kobain Ken 1001
16 Chen Wei-Wei 1001
50 Kim Yong 1001
.
.
.
20 Blake William 4002
20 rows selected.