SQL – INSERT INTO STATEMENT
Insert statement is a basic yet most useful command in SQL to insert new data (records) into the table in the database. There are various ways in which data can be inserted into a table. For instance, you can insert data into specific columns of a table or insert data in the entire row (consisting of all columns) of a table or you can even insert data into multiple rows at once. Likewise, we have other ways to achieve the same which you can learn below.
Scenario 1 – Inserting data in a complete row (Includes all the columns of a table)
There are two ways to achieve this type of insertion.
Method 1 – Insertion of data into the table by specifying only values
Using this method, you need to specify only values and not column names. However, if you are using this, you have to remember the mandatory points mentioned below.
- You are supposed to supply a value for every column that exists in the table in which data is being inserted.
- Order of values mentioned should be similar to the order of columns in the table.
SYNTAX
| INSERT INTO table_name VALUES ( value_1, value_2, …….value_(n – 1),value_(n) ); Here, table_name: Name of table into which data is being inserted. value_1, value_2 … value_(n): Values for all the columns in table ‘table_name’ followed in order. |
Note:
While inserting values we need to consider the following key points.
- String data types: The String or character values should always be enclosed in single quotes.
- Numeric data types: The numeric (integer, float) values need not be enclosed in single quotes.
- Date data types: The date values must always be enclosed in single quotes.
Look at the example below which explains all the above points.
| INSERT INTO EMPLOYEES VALUES (101, ‘DIEM BROWN’, ‘M’, ’01-JAN-15′, ‘Senior Analyst’); |
Example (Specifying only values)
Consider the sample table EMPLOYEES looks like below which has just one record as of now.
Let us insert the data using the 1st method where we insert two new records by specifying just values and not the column names.
| INSERT INTO EMPLOYEES VALUES (101, ‘DIEM BROWN’, ‘M’, ’01-JAN-15′, ‘Senior Analyst’); INSERT INTO EMPLOYEES VALUES (102, ‘Grim GILLBERT’, ‘M’, ’16-MAR-10′, ‘Team Lead’); |
Output
Look at the table data by using the query mentioned below
select * from EMPLOYEES;
We find that two new records have been added.
Method 2 – Insertion of data into the table by specifying both column names and values
Using this method, you need to specify both column names and values. Since column names are specified in the query, there is no necessity to follow the order of columns in the table while specifying the values.
SYNTAX
| INSERT INTO table_name (column_1, column_2, …….. column_(n – 1), column_(n)) VALUES ( value_1, value_2, ……. value_(n – 1), value_(n) ); Here, table_name: Name of the table in which you are inserting data. column_1, column_2: Names of columns in the table ‘table_name’. value_1, value_2: Values supplied for the colum1 and column2. |
Example (Specifying both column names and values)
Let us proceed to add two more records to the EMPLOYEES table using 2nd syntax.
| INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, EMP_GENDER, DATE_OF_JOIN, DESIGNATION) VALUES (103, ‘DWAYNE BRAVO’, ‘M’, ’07-AUG-08′, ‘HR’); INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, EMP_GENDER, DATE_OF_JOIN, DESIGNATION) VALUES (105, ‘SOFIA CECCON’, ‘F’, ’24-NOV-13′, ‘Business Analyst’); |
Output
You can now use the select query and look at the EMPLOYEES table which will now contain two newly inserted records along with old ones.
Scenario 2 – Inserting data only in specific columns of a row
This syntax is useful when you want to insert a record but do not want to insert values for all columns in a table. In such cases, you specify only those column names and corresponding values in the query in which you want to store valid values. Columns that were not mentioned in the query will store null values by default.
SYNTAX
| INSERT INTO table_name (column_3, column_7, column_1) VALUES ( value_3, value_7, value_1 ); Note that column_3, column_7, column_1 are not in order which is completely fine unless and until you are providing their values in the same order as that of their columns. |
Example
Consider a scenario where it is decided not to supply any value for the DATE_OF_JOIN column for new associates joining the company. In such a case, you skip that column name in the column list.
You may use below insert queries
| INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (104, ‘AUGUSTINE’, ‘M’, ‘Associate Trainee’); INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (106, ‘RICKY MARTIN, ‘M’, ‘Associate Trainee’); |
Output
As you can notice below, DATE_OF_JOIN for the newly inserted records will have null values.
Scenario 3 – Inserting data in multiple rows at once
Rather than using multiple insert queries to insert multiple records, we can use another syntax which allows us to insert multiple rows at a time into a single statement. However, it is important to note that different syntax is used for different databases.
The syntax used for MYSQL and ORACLE has been mentioned below.
SYNTAX used in MYSQL database
| INSERT INTO table-name (column_1, column_2, column_3) VALUES (list_of_values_1), (list_of_values_2), ….. ….. ….. (list_of_values_n); Here, list_of_values_1 is a set of values separated by a comma which are to be inserted into 1st row of a table. list_of_values_2 is a set of values to be inserted into the 2nd row of a table and so on. |
Example (Multiple rows at once)
Notice the below insert queries which if executed adds 5 records to table EMPLOYEES_LIST. But it is not preferred when there is a requirement to add more number of records (say 100) because then we have to write those many insert queries.
| INSERT INTO EMPLOYEES_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (100, ‘SANSA’, ‘F’, ‘Associate Trainee’); INSERT INTO EMPLOYEES_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (101, ‘DIEM’, ‘M’, ‘Senior Analyst’); INSERT INTO EMPLOYEES_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (102, ‘GRIM ‘, ‘M’, ‘Team Lead’); INSERT INTO EMPLOYEES_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (103, ‘BENTLEY’, ‘M’, ‘HR’); INSERT INTO EMPLOYEES_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (104, ‘SOFIA’, ‘F’, ‘Business Analyst’); |
To overcome this, there is another syntax which uses single statement producing similar result to above multiple insert queries.
Consider the table EMPLOYEES_LIST is empty and we insert multiple records at time using the below single statement insert query.
| INSERT INTO EMPLOYEES_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (100, ‘SANSA’, ‘F’, ‘Associate Trainee’), (101, ‘DIEM’, ‘M’, ‘Senior Analyst’), (102, ‘Grim ‘, ‘M’, ‘Team Lead’), (103, ‘BENTLEY’, ‘M’, ‘HR’), (104, ‘SOFIA’, ‘F’, ‘Business Analyst’); |
Output
As you can see below, five new rows are inserted at once.
5 row(s) inserted
SYNTAX used in ORACLE database
There are two different ways in which we can insert data into multiple rows in the ORACLE database.
Syntax 1: Uses INSERT ALL along with the subquery.
Syntax 2: Uses INSERT INTO along with UNION ALL.
Syntax 1
| INSERT ALL INTO table_name (column_1, column_2 … column_n) VALUES (value_1, value_2 … value_n) INTO table_name (column_1, column_2 … column_n) VALUES (value_1, value_2 … value_n) INTO table_name (column_1, column_2 … column_n) VALUES (value_1, value_2 … value_n) SELECT 1 FROM dual; Here, INSERT ALL needs a SELECT subquery. To get around with that, SELECT 1 FROM dual is used to provide a single record/row of dummy data. An interesting point using this syntax is table_name specified in the above syntax can also be different tables which signify that we can even insert data into different tables at once. |
Example 1 – INSERT ALL – (Multiple rows at once in a single table)
Consider EMPLOYEE_LIST is empty now with no records (use “truncate table EMPLOYEE_LIST” to empty the table). Let us insert some data using below.
| INSERT ALL INTO EMPLOYEE_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (100, ‘SANSA’, ‘F’, ‘Associate Trainee’) INTO EMPLOYEE_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (101, ‘DIEM’, ‘M’, ‘Senior Analyst’) INTO EMPLOYEE_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (102, ‘GRIM ‘, ‘M’, ‘Team Lead’) INTO EMPLOYEE_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (103, ‘BENTLEY’, ‘M’, ‘HR’) INTO EMPLOYEE_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (104, ‘SOFIA’, ‘F’, ‘Business Analyst’) SELECT 1 FROM DUAL; |
Output
5 row(s) inserted
Example 2 – INSERT ALL – (Multiple rows at once in different tables)
Let us consider two tables EMPLOYEE_LIST and JOBS with no records
select * from EMPLOYEE_LIST;
no data foundselect * from JOBS;
no data found| INSERT ALL INTO EMPLOYEE_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (100, ‘SANSA’, ‘F’, ‘Associate Trainee’) INTO EMPLOYEE_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (101, ‘DIEM’, ‘M’, ‘Senior Analyst’) INTO EMPLOYEE_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) VALUES (102, ‘GRIM ‘, ‘M’, ‘Team Lead’) INTO JOBS (JOB_ID, DESIGNATION, TYPE_OF_JOB, WORKING_HOURS) values (‘JF’, ‘Junior faculty’, ‘PART TIME’, 4) INTO JOBS (JOB_ID, DESIGNATION, TYPE_OF_JOB, WORKING_HOURS) values (‘SF’, ‘Senior faculty’, ‘FULL TIME’, 8) SELECT 1 FROM DUAL; |
Checking the contents of the tables mentioned above, we find that new records have been inserted.
select * from EMPLOYEE_LIST;
select * from JOBS;
Syntax 2
| INSERT INTO table_name(column_1, column_2 … column_n) WITH alias_name AS ( SELECT value_1, value_2 … value_n FROM dual UNION ALL SELECT value_1, value_2 … value_n FROM dual UNION ALL SELECT value_1, value_2 … value_n FROM dual UNION ALL SELECT value_1, value_2 … value_n FROM dual ) SELECT * FROM alias_name; Here, column_1, column_2 … column_n are column names into which values have to be inserted. alias_name is the name used to refer to the dummy table that we create by using multiple select queries along with union_all. In the last line, we see ‘SELECT * FROM alias_name’ which ultimately selects all the records and puts into the corresponding table. |
Example (Multiple rows at once) – INSERT INTO with UNION ALL
Consider EMPLOYEE_LIST is empty with no records (use “truncate table EMPLOYEE_LIST” to empty the table). Insert the data using the below query.
| INSERT INTO EMPLOYEE_LIST (EMP_ID, EMP_NAME, EMP_GENDER, DESIGNATION) WITH names AS ( SELECT 100, ‘SANSA’, ‘F’, ‘Associate Trainee’ FROM dual UNION ALL SELECT 101, ‘DIEM’, ‘M’, ‘Senior Analyst’ FROM dual UNION ALL SELECT 102, ‘GRIM ‘, ‘M’, ‘Team Lead’ FROM dual UNION ALL SELECT 103, ‘BENTLEY’, ‘M’, ‘HR’ FROM dual UNION ALL SELECT 104, ‘SOFIA’, ‘F’, ‘Business Analyst’ FROM dual ) SELECT * FROM names; |
SELECT * FROM EMPLOYEE_LIST;
Output
5 row(s) inserted
Scenario 4 – Inserting data in one table by using the data from the other table
This allows us to copy the data from one table to another table.
There are two ways – One is to copy the entire data in one table from the other. And the other is to copy only a few columns of data into table’s columns. However, note that the datatype of the column from which value is copied and the datatype of the column to which the copied data will be inserted should always be similar.
Method 1 – Insertion of values into all the columns of table 1 from table 2.
SYNTAX
| INSERT INTO table_1 SELECT * FROM table_2 [WHERE condition]; Here, table_1 is a table in which data is being inserted. table_2 is a table from which data is being copied. Condition is any condition that can be mentioned in where clause to filter rows from table_2. |
Example
Sample table – TEAM1
Let us now insert all the records from the TEAM_1 table into TEAM_2.
| INSERT INTO TEAM_2 SELECT * FROM TEAM_1; |
Output
Check the content of TEAM_2 using the query
SELECT * FROM TEAM_2
It displays 6 rows similar to the rows present in the TEAM_1 table.
6 row(s) inserted
Method 2 – Insertion of values into specific columns of table 1 from table 2.
SYNTAX
| INSERT INTO table_2 (column_a, column_b, column_c) SELECT column_x, column_y, column_z FROM table_1 [WHERE condition]; Here, table_1, table_2, and condition are already explained in Method 1 syntax. column_a, column_b, column_c are column names of table_2 in which values are to be inserted. column_x, column_y, column_z are column names of table_1 from which values are to be fetched and copied. |
Example 2
Let us insert two more records in TEAM_1.
| INSERT INTO TEAM_1 (STUDENT_ID, STUDENT_NAME, PHONE_NUMBER, EMAIL) VALUES (7, ‘ADAM SMITH’, 7748972878, ‘asmith@yahoo.com’); INSERT INTO TEAM_1 (STUDENT_ID, STUDENT_NAME, PHONE_NUMBER, EMAIL) VALUES (8, ‘JOSEPH STALIN’, 8974873738, ‘stalin@hotmail.com’) |
TEAM_1 looks like
TEAM_2 looks like
Considering the above results of TEAM_1 and TEAM_2 tables, let us now filter two columns that are only present in TEAM_1 (with STUDENT_ID – 7 and 8)and then add those in TEAM_2 using 2nd syntax where only a few columns are mentioned. Notice that we are not mentioning the column PHONE_NUMBER while data is being inserted from TEAM_1 to TEAM_2 and therefore by default null values are being inserted.
| INSERT INTO TEAM_2 (STUDENT_ID, STUDENT_NAME, EMAIL) SELECT STUDENT_ID, STUDENT_NAME, EMAIL FROM TEAM_1 WHERE STUDENT_ID IN (7, 8); |
Run the below query now.
SELECT * FROM TEAM_2;
Output
2 row(s) inserted
Leave a Reply