DBA Hub

📋Steps in this guide1/6

SQL for Beginners (Part 8) : The INSERT Statement

In this article we take a look at some of the variations on the INSERT statement.

oracle miscconfigurationintermediate
by OracleDba
12 views
1

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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
--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;

-- 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)
);
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 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 statement. Many of the examples in this article will issue 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 statement after every DML statement they process. Don't let this fool you into thinking this is default behaviour. It is not.
3

Basic INSERT

The simplest version of the 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. Specifying a column list is much safer as the statement will not break if optional columns are added to the table. 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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
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;

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;

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;

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>
4

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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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;

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;
5

INSERT ... SELECT

It is possible to create multiple rows in a single statement using the syntax. The following example copies all the data from the EMPLOYEES table into the EMPLOYEES2 table. The column list in the statement must match the list in the query. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
-- 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>

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>

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>
6

Scalar Subqueries

Scalar subqueries can be used to source a value for an 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. For more information see: - SQL for Beginners (Part 8) : The INSERT Statement - SQL for Beginners - Full Playlist - Oracle SQL Articles - Getting Started - Database SQL Language Reference : INSERT - Multitable Inserts Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!