Usage of SQL INSERT INTO select

INSERT into SELECT statement

Statement form: Insert into Table2 (field1,field2,...) Select Value1,value2,... from Table1

The target table Table2 must exist, because the target table Table2 already exists, so we can insert a constant in addition to the fields in the source table Table1

Sql>
Sql>
Sql> CREATE TABLE Employee (
2 ID number,
3 name varchar (100),
4 birth_date Date,
5 gender VARCHAR2 (30));

Table created.

Sql>
Sql>
Sql> INSERT into employee (Id,name,birth_date,gender)
2 SELECT, ' Chris ', NULL, ' MALE ' from DUAL;

1 row created.

Sql>
Sql>
sql> drop table employee;

Table dropped.

SELECT into from statement

Statement form: SELECT vale1, value2 into Table2 from Table1

The target table Table2 does not exist because the table Table2 is created automatically at insert time and the specified field data in Table1 is copied to Table2. Examples are as follows:

With conditional selection where

Sql>
Sql> CREATE TABLE Employee (
2 ID number,
3 name varchar (100),
4 birth_date Date,
5 gender VARCHAR2 (30));

Table created.

Sql>
Sql> INSERT into employee (id,name,birth_date,gender) SELECT, ' H ', NULL, ' MALE ' from dual D
2 where NOT EXISTS (SELECT 1 from employee x where x.id = ' 300 ');

1 row created.

Sql>
Sql> SELECT * from employee;

Id
------
NAME
----------------------------------------------------------------------
Birth_date GENDER
---------- ------------------------------
300
H
Null MALE

Insert bulk by insert ... into ... select

Sql>
Sql> CREATE TABLE Project (
2 pro_id Number (4),
3 Pro_name VARCHAR2 (40),
4 Budget Number (9,2),
5 CONSTRAINT project_pk PRIMARY KEY (pro_id)
6);

Table created.

Sql>
Sql>
Sql> INSERT into Project (pro_id, Pro_name, Budget) VALUES (1001, ' A ', 12345);

1 row created.

Sql> INSERT into Project (pro_id, Pro_name, Budget) VALUES (1002, ' ERP ', 23456);

1 row created.

Sql> INSERT into Project (pro_id, Pro_name, Budget) VALUES (1003, ' SQL ', 34567);

1 row created.

Sql> INSERT into Project (pro_id, Pro_name, Budget) VALUES (1004, ' CRM ', 45678);

1 row created.

Sql> INSERT into Project (pro_id, Pro_name, Budget) VALUES (1005, ' VPN ', 56789);

1 row created.

Sql>
Sql>
Sql> SET ECHO on
Sql> INSERT into PROJECT (pro_id, Pro_name)
2 SELECT pro_id+8000,
3 SUBSTR (pro_name,1,31) | | ' Overhead '
4 from Project;

5 rows created.