SQL for Beginners (Part 8) : The INSERT Statement

This is the eighth part of a series of articles showing the basics of SQL. In this article we take a look at some of the variations on the INSERT statement.

Setup
COMMIT and ROLLBACK
Basic INSERT
INSERT into View
INSERT ... SELECT
Scalar Subqueries

Setup

You can perform all these queries online for free using SQL Fiddle.

The examples in this article require the following tables to be present.

--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;
CREATE TABLE departments (
 department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
 department_name VARCHAR2(14),
 location VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
CREATE TABLE employees (
 employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
 employee_name VARCHAR2(10),
 job VARCHAR2(9),
 manager_id NUMBER(4),
 hiredate DATE,
 salary NUMBER(7,2),
 commission NUMBER(7,2),
 department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.

In this article, we are also going to need the following copy of the EMPLOYEES table. Notice we've made the EMPLOYEE_NAME and JOB columns mandatory. The EMPLOYEE_ID column is implicitly mandatory, as a result of it being the primary key.

-- DROP TABLE employees2 PURGE;
CREATE TABLE employees2 (
 employee_id NUMBER(4) CONSTRAINT employees2_pk PRIMARY KEY,
 employee_name VARCHAR2(10) NOT NULL,
 job VARCHAR2(9) NOT NULL,
 manager_id NUMBER(4),
 hiredate DATE,
 salary NUMBER(7,2),
 commission NUMBER(7,2),
 department_id NUMBER(2)
);

COMMIT and ROLLBACK

All Data Manipulation Language (DML) changes are done as part of a transaction. They are not permanent until they are confirmed using the COMMIT statement. Once committed, the only way you can revert a change is to issue a new DML statement to alter the data. Several statements can be grouped together to form a single transaction.

Data Definition Language (DDL) commands perform an implicit commit, which also confirms all outstanding DML changes in the current session.

If you decide you don't want to keep some uncommitted changes, you can throw them away using using the ROLLBACK statement. Many of the examples in this article will issue ROLLBACK statements after the test, to revert the data to its original state.

Some tools and programming languages default to auto-commit, so they automatically issue a COMMIT statement after every DML statement they process. Don't let this fool you into thinking this is default behaviour. It is not.

Basic INSERT

The simplest version of the INSERT statement doesn't specify the columns to be inserted. It assumes the values are specified in the correct column order and no column values have been omitted. As a result, this is dangerous in application code as adding a column to the table will break the statement.

INSERT INTO employees2 VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
SELECT *
FROM employees2
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
 8888 JONES CLERK 7902 17-DEC-1980 00:00:00 800 20
1 row selected.
SQL>
ROLLBACK;

Specifying a column list is much safer as the statement will not break if optional columns are added to the table.

INSERT INTO employees2 (employee_id, employee_name, job, manager_id, hiredate, salary, commission, department_id)
VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
SELECT *
FROM employees2
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
 8888 JONES CLERK 7902 17-DEC-1980 00:00:00 800 20
1 row selected.
SQL>
ROLLBACK;

An explicit column list also allows you to remove optional columns from the statement and swap the positions of the columns in the statement. In the following example the MANAGER_ID, COMMISSION and DEPARTMENT_ID columns have been removed and the EMPLOYEE_ID column has been moved to the end of the column list and value list.

INSERT INTO employees2 (employee_name, job, hiredate, salary, employee_id)
VALUES ('JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),800,8888);
SELECT *
FROM employees2
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
 8888 JONES CLERK 7902 17-DEC-1980 00:00:00 800 20
1 row selected.
SQL>
ROLLBACK;

Remember, missing out mandatory columns is not allowed, unless you have column default values or triggers which populate them. In the following example the JOB column is omitted, which is a mandatory column in the EMPLOYEES2 table.

INSERT INTO employees2 (employee_id, employee_name, manager_id, hiredate, salary, commission, department_id)
VALUES (8888,'JONES',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees2 (employee_id, employee_name, manager_id, hiredate, salary, commission, department_id)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."EMPLOYEES2"."JOB")
SQL>

INSERT into Views

It's possible to insert into the base table associated with a view. There are some restrictions associated with this, but they are a little out of scope for a beginner level article. In the following example, we create a simple view on the EMPLOYEES2 table, then insert into it.

CREATE OR REPLACE VIEW employees2_v AS
SELECT * FROM employees2;
INSERT INTO employees2_v VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
SELECT *
FROM employees2
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
 8888 JONES CLERK 7902 17-DEC-1980 00:00:00 800 20
1 row selected.
SQL>
ROLLBACK;

You won't see it very often, but you can also insert into inline views. This can be used to control the column list for the insert, rather than using an explicit column list.

INSERT INTO (SELECT employee_id, employee_name, job, hiredate, salary
 FROM employees2)
VALUES (8888,'JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),800);
SELECT *
FROM employees2
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
 8888 JONES CLERK 7902 17-DEC-1980 00:00:00 800 20
1 row selected.
SQL>
ROLLBACK;

INSERT ... SELECT

It is possible to create multiple rows in a single INSERT statement using the INSERT ... SELECT syntax. The following example copies all the data from the EMPLOYEES table into the EMPLOYEES2 table.

-- Make sure the table is empty.
TRUNCATE TABLE employees2;
INSERT INTO employees2
SELECT * FROM employees;
COMMIT;
SELECT *
FROM employees2
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
 7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800 20
 7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300 30
 7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500 30
 7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975 20
 7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400 30
 7698 BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850 30
 7782 CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450 10
 7788 SCOTT ANALYST 7566 19-APR-1987 00:00:00 3000 20
 7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000 10
 7844 TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0 30
 7876 ADAMS CLERK 7788 23-MAY-1987 00:00:00 1100 20
 7900 JAMES CLERK 7698 03-DEC-1981 00:00:00 950 30
 7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000 20
 7934 MILLER CLERK 7782 23-JAN-1982 00:00:00 1300 10
14 rows selected.
SQL>

The column list in the INSERT statement must match the SELECT list in the query.

TRUNCATE TABLE employees2;
INSERT INTO employees2 (employee_id, employee_name, job, hiredate, salary)
SELECT employee_id, employee_name, job, hiredate, salary
FROM employees;
COMMIT;
SELECT *
FROM employees2
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
 7369 SMITH CLERK 17-DEC-1980 00:00:00 800
 7499 ALLEN SALESMAN 20-FEB-1981 00:00:00 1600
 7521 WARD SALESMAN 22-FEB-1981 00:00:00 1250
 7566 JONES MANAGER 02-APR-1981 00:00:00 2975
 7654 MARTIN SALESMAN 28-SEP-1981 00:00:00 1250
 7698 BLAKE MANAGER 01-MAY-1981 00:00:00 2850
 7782 CLARK MANAGER 09-JUN-1981 00:00:00 2450
 7788 SCOTT ANALYST 19-APR-1987 00:00:00 3000
 7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000
 7844 TURNER SALESMAN 08-SEP-1981 00:00:00 1500
 7876 ADAMS CLERK 23-MAY-1987 00:00:00 1100
 7900 JAMES CLERK 03-DEC-1981 00:00:00 950
 7902 FORD ANALYST 03-DEC-1981 00:00:00 3000
 7934 MILLER CLERK 23-JAN-1982 00:00:00 1300
14 rows selected.
SQL>

The normal rules apply for query portion of the statement, so you can filter the data or join to other tables to gather the data.

TRUNCATE TABLE employees2;
INSERT INTO employees2
SELECT *
FROM employees
WHERE department_id = 20;
COMMIT;
SELECT *
FROM employees2
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
 7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800 20
 7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975 20
 7788 SCOTT ANALYST 7566 19-APR-1987 00:00:00 3000 20
 7876 ADAMS CLERK 7788 23-MAY-1987 00:00:00 1100 20
 7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000 20
5 rows selected.
SQL>

Scalar Subqueries

Scalar subqueries can be used to source a value for an INSERT statement. In the example below, the SALARY column is populated by a scalar subquery, which returns the maximum SALARY from the EMPLOYEES table and adds 1000 to it.

TRUNCATE TABLE employees2;
INSERT INTO employees2 (employee_id, employee_name, job, hiredate, salary)
VALUES (8888, 'JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),(SELECT MAX(salary)+1000 FROM employees));
SELECT *
FROM employees2
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
 8888 JONES CLERK 17-DEC-1980 00:00:00 6000
1 row selected.
SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)