8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
Related articles.
- SQL for Beginners (Part 8) : The INSERT Statement
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
- Multitable Inserts
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:
- 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...