SQL INSERT INTO Statement

The INSERT INTO statement in SQL is used to add new records to a table in a database. It is a fundamental command for data insertion and is used to insert new data into tables.

Syntax

There are two syntaxes of INSERT INTO statements depending on the requirements. The two syntaxes are:

1. Only Values

The first method is to specify only the value of data to be inserted without the column names.

INSERT INTO table_name
VALUES (value1, value2, value); 

Here,

• table_name: name of the table.
• value1, value2: value of first column, second column,… for the new record

2. Column Names And Values Both

In the second method we will specify both the columns which we want to fill and their corresponding values as shown below:

INSERT INTO table_name (column1, column2, column3) 
VALUES ( value1, value2, value);

Here,

• table_name: name of the table. 
• column1, column2..: name of first column, second column.
• value1, value2, value..:  value of first column, second column,… for the new record

SQL INSERT INTO Examples

Let’s look at some examples of INSERT INTO statement in SQL to understand it better.

Suppose there is a Student database and we want to add values. 

ROLL_NO	NAME	ADDRESS	PHONE	AGE
1	Ram	Delhi	xxxxxxxxxxxxxx	18
2	RAMESH	GURGAON	xxxxxxxxxxxxxx	18
3	SUJIT	ROHTAK	xxxxxxxxxxxxxx	20
4	SURESH	ROHTAK	xxxxxxxxxxxxxx	18
3	SUJIT	ROHTAK	xxxxxxxxxxxxxx	20
2	RAMESH	GURGAON	xxxxxxxxxxxxxx	18

Inserting Only New Values Using INSERT INTO Example

If we want to insert only values then we use the following query:

Query:

INSERT INTO Student 
VALUES ('5','HARSH','WEST BENGAL', 'XXXXXXXXXX','19');

Output:

The table Student will now look like this:

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	XXXXXXXXXX	18
2	RAMESH	GURGAON	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
4	SURESH	Delhi	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
2	RAMESH	GURGAON	XXXXXXXXXX	18
5	HARSH	WEST BENGAL	XXXXXXXXXX	19

Insert Values to Specified Columns Using INSERT INTO Example

If we want to insert values in the specified columns then we use the following query:

Query:

INSERT INTO Student (ROLL_NO, NAME, Age) 
VALUES ('5','PRATIK','19');

Output:

The table Student will now look like this:

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	XXXXXXXXXX	18
2	RAMESH	GURGAON	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
4	SURESH	Delhi	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
2	RAMESH	GURGAON	XXXXXXXXXX	18
5	PRATIK	null	null	19

Notice that the columns for which the values are not provided are filled by null. Which are the default values for those columns?

Insert Multiple Rows in a table using Single SQL Statement

You can use the given technique to insert multiple rows in a table in a single query. This saves time for writing queries, and reduces the margin error.

Syntax:

INSERT INTO table_name(Column1,Column2,Column3,…….) 
VALUES (Value1, Value2,Value3,…..),
        (Value1, Value2,Value3,…..),
         (Value1, Value2,Value3,…..),
         ……………………….. ;

Where,

• table_name: name of the table.
  Column 1: name of the first column, second column.
• Values: Value1, Value2, Value3: the value of the first column, second column.
• For each new row inserted, you need To provide Multiple lists of values where each list is separated by “,”. Every list of values corresponds to values to be inserted in each new row of the table. Values in the next list tell values to be inserted in the next Row of the table.

Insert Multiple Rows in a table using Single SQL Statement Example:

The following SQL statement inserts multiple rows in Student Table.

Query:

INSERT INTO STUDENT (ID, NAME,AGE,GRADE,CITY) 
VALUES
(1,"AMIT KUMAR",15,10,"DELHI"),
(2,"GAURI RAO",18,12,"BANGALORE"),
(3,"MANAV BHATT",17,11,"NEW DELHI"),
(4,"RIYA KAPOOR",10,5,"UDAIPUR");

Output:

Thus STUDENT Table will look like this:

ID	NAME	AGE	GRADE	CITY
1	AMIT KUMAR	15	10	DELHI
2	GAURI RAO	18	12	BANGALORE
3	MANAV BHATT	17	11	NEW DELHI
4	RIYA KAPOOR	10	5	UDAIPUR

SQL INSERT INTO SELECT

The SQL INSERT INTO SELECT statement is used to copy data from one table and insert it into another table. The use of this statement is similar to that of the INSERT INTO statement. The difference is that the SELECT statement is used here to select data from a different table. The different ways of using INSERT INTO SELECT statement are shown below:

INSERT INTO SELECT Syntax

There are two syntaxes for using INSERT INTO SELECT statement, depending on it’s use.

Copy All Columns and Insert

The syntax for using INSERT INTO SELECT query to insert all data from a table to another table:

INSERT INTO first_table SELECT * FROM second_table;

Here,

• first_table: name of first table. 
• second_table: name of second table.

We have used the SELECT statement to copy the data from one table and the INSERT INTO statement to insert from a different table.

Copy Specific Columns and Insert 

The syntax for using INSERT INTO SELECT query to insert specific data from a table to another table:

INSERT INTO first_table(names_of_columns1) 
SELECT names_of_columns2 FROM second_table; 

Here,

• first_table: name of first table. second_table: name of second table.
• names of columns1: name of columns separated by comma(,) for table 1.
• names of columns2: name of columns separated by comma(,) for table 2.

We have used the SELECT statement to copy the data of the selected columns only from the second table and the INSERT INTO statement to insert in the first table.

Copy Specific Rows and Insert 

We can copy specific rows from a table to insert into another table by using the WHERE clause with the SELECT statement. We have to provide appropriate conditions in the WHERE clause to select specific rows.

The syntax for using INSERT INTO SELECT query to insert specific rows from table

INSERT INTO table1 SELECT * FROM table2 WHERE condition; 

Here,

• first_table: name of first table.
• second_table: name of second table. 
• condition: condition to select specific rows.

SQL INSERT INTO SELECT Examples

Let’s look at some examples of INSERT INTO SELECT statement to understand it better.

Suppose there is a LateralStudent database.

ROLL_NO	NAME	ADDRESS	PHONE	Age
7	SOUVIK	HYDERABAD	XXXXXXXXXX	18
8	NIRAJ	NOIDA	XXXXXXXXXX	19
9	SOMESH	ROHTAK	XXXXXXXXXX	20

Inserting all rows and columns using INSERT INTO SELECT example

If we want to insert only values then we use the following query:

Query:

INSERT INTO Student 
SELECT * FROM LateralStudent;

Output:

This query will insert all the data of the table LateralStudent in the table Student. The table Student will now look like this,

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	XXXXXXXXXX	18
2	RAMESH	GURGAON	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
4	SURESH	Delhi	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
2	RAMESH	GURGAON	XXXXXXXXXX	18
7	SOUVIK	DUMDUM	XXXXXXXXXX	18
8	NIRAJ	NOIDA	XXXXXXXXXX	19
9	SOMESH	ROHTAK	XXXXXXXXXX	20

Inserting specific columns using INSERT INTO SELECT example

If we want to insert values in the specified columns then we use the following query:

Query:

INSERT INTO Student(ROLL_NO,NAME,Age) 
SELECT ROLL_NO, NAME, Age FROM LateralStudent;

Output:

This query will insert the data in the columns ROLL_NO, NAME, and Age of the table LateralStudent in the table Student and the remaining columns in the Student table will be filled by null which is the default value of the remaining columns. The table Student will now look like this,

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	XXXXXXXXXX	18
2	RAMESH	GURGAON	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
4	SURESH	Delhi	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
2	RAMESH	GURGAON	XXXXXXXXXX	18
7	SOUVIK	null	null	18
8	NIRAJ	null	null	19
9	SOMESH	null	null	20

Insert specific rows using INSERT INTO SELECT example:

INSERT INTO Student 
SELECT * FROM LateralStudent WHERE Age = 18;

Output:

This query will select only the first row from table LateralStudent to insert into the table Student. The table Student will now look like this,

ROLL_NO	NAME	ADDRESS	PHONE	Age
1	Ram	Delhi	XXXXXXXXXX	18
2	RAMESH	GURGAON	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
4	SURESH	Delhi	XXXXXXXXXX	18
3	SUJIT	ROHTAK	XXXXXXXXXX	20
2	RAMESH	GURGAON	XXXXXXXXXX	18
7	SOUVIK	DUMDUM	XXXXXXXXXX	18

Important Points About SQL INSERT INTO Statement

• The INSERT INTO statement is used to add new records to a table in a database
• It allows inserting multiple records in a single statement by providing multiple sets of values.
• If you don’t specify the column names, the statement assumes all columns and the values must be in the same order as the table definition.
• Columns not included in the INSERT statement will be filled with default values, which are typically NULL.
• statementscondition.