INSERT statement

An INSERT statement creates a row or rows and stores them in the named table. The number of values assigned in an INSERT statement must be the same as the number of specified or implied columns.

Syntax

INSERT INTO table-Name
    [ (Simple-column-Name [ , Simple-column-Name]* ) ]
	  Query
Query can be:
  • a SelectExpression
  • a VALUES list
  • a multiple-row VALUES expression

    Single-row and multiple-row lists can include the keyword DEFAULT. Specifying DEFAULT for a column inserts the column's default value into the column. Another way to insert the default value into the column is to omit the column from the column list and only insert values into other columns in the table. For more information see VALUES Expression.

  • UNION expressions

For more information about Query, see Query.

INSERT INTO COUNTRIES
      VALUES ('Taiwan', 'TW', 'Asia');

-- Insert a new department into the DEPARTMENT table,
-- but do not assign a manager to the new department
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
	VALUES ('E31', 'ARCHITECTURE', 'E01');
-- Insert two new departments using one statement 
-- into the DEPARTMENT table as in the previous example, 
-- but do not assign a manager to the new department.
INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME, ADMRDEPT)
	VALUES ('B11', 'PURCHASING', 'B01'),
	('E41', 'DATABASE ADMINISTRATION', 'E01');
--  Create a temporary table MA_EMP_ACT with the 
-- same columns as the EMP_ACT table. 
-- Load MA_EMP_ACT with the rows from the EMP_ACT
-- table with a project number (PROJNO) 
-- starting with the letters 'MA'.
CREATE TABLE MA_EMP_ACT 
    (
	EMPNO CHAR(6)  NOT NULL, 
	PROJNO CHAR(6)  NOT NULL, 
	ACTNO SMALLINT  NOT NULL, 
	EMPTIME DEC(5,2), 
	EMSTDATE DATE, 
	EMENDATE  DATE
    );

INSERT INTO MA_EMP_ACT
	SELECT * FROM EMP_ACT
	WHERE SUBSTR(PROJNO, 1, 2) = 'MA';
-- Insert the DEFAULT value for the LOCATION column
INSERT INTO DEPARTMENT
      VALUES ('E31', 'ARCHITECTURE', '00390', 'E01', DEFAULT);

Statement dependency system

The INSERT statement depends on the table being inserted into, all of the conglomerates (units of storage such as heaps or indexes) for that table, and any other table named in the query. Any statement that creates or drops an index or a constraint for the target table of a prepared INSERT statement invalidates the prepared INSERT statement.

Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE statement
CALL (PROCEDURE)
CONSTRAINT clause
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
INNER JOIN
JOIN operation
LEFT OUTER JOIN
LOCK TABLE statement
ORDER BY clause
Query
RIGHT OUTER JOIN
ScalarSubquery
SelectExpression
SELECT statement
TableExpression
TableSubquery
UPDATE statement
VALUES expression
WHERE clause
WHERE CURRENT OF clause