Open In App
Related Articles

SQL INSERT INTO Statement

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report

The INSERT INTO statement of SQL is used to insert a new row/record in a table. There are two ways of using the SQL INSERT INTO statement for inserting rows.

SQL INSERT Query

1. Only Values

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

INSERT INTO Syntax:

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

table_name: name of the table. value1, value2 

value of first column, second column,… for the new record

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 Data in Specified Columns – Syntax:

INSERT INTO table_name (column1, column2, column3) 

VALUES ( value1, value2, value3); table_name: 

name of the table. 

column1: name of first column, second column .
value1, value2, value3  value of first column, second column,… for the new record

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

ROLL_NONAME ADDRESS PHONEAGE
1RamDelhixxxxxxxxxxxxxx18
2RAMESHGURGAONxxxxxxxxxxxxxx18
3SUJITROHTAKxxxxxxxxxxxxxx20
4SURESHROHTAKxxxxxxxxxxxxxx18
3SUJITROHTAKxxxxxxxxxxxxxx20
2RAMESHGURGAONxxxxxxxxxxxxxx18

Method 1 (Inserting only values) – SQL INSERT Query

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_NONAMEADDRESSPHONEAge
1RamDelhiXXXXXXXXXX18
2RAMESHGURGAONXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
4SURESHDelhiXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
2RAMESHGURGAONXXXXXXXXXX18
5HARSHWEST BENGALXXXXXXXXXX19

Method 2 (Inserting values in only specified columns) – SQL INSERT INTO Statement

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_NONAMEADDRESSPHONEAge
1RamDelhiXXXXXXXXXX18
2RAMESHGURGAONXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
4SURESHDelhiXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
2RAMESHGURGAONXXXXXXXXXX18
5PRATIKnullnull19

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

2. Using SELECT in INSERT INTO Statement

We can use the SELECT statement with INSERT INTO statement to copy rows from one table and insert them 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:

Inserting all columns of a table – INSERT INTO SELECT Statement 

We can copy all the data of a table and insert it into a different table.

Syntax:

INSERT INTO first_table SELECT * FROM second_table;

 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.

Inserting specific columns of a table – INSERT INTO SELECT Statement 

We can copy only those columns of a table that we want to insert into a different table.

Syntax:

INSERT INTO first_table(names_of_columns1) 

SELECT names_of_columns2 FROM second_table; 

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.

Copying specific rows from a table – INSERT INTO SELECT Statement 

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.

INSERT INTO table1 SELECT * FROM table2 WHERE condition; 

first_table: name of first table.

 second_table: name of second table. 

condition: condition to select specific rows.

Suppose there is a LateralStudent database.

ROLL_NONAMEADDRESSPHONEAge
7SOUVIKHYDERABADXXXXXXXXXX18
8NIRAJNOIDAXXXXXXXXXX19
9SOMESHROHTAKXXXXXXXXXX20

 

Method 1 – (Inserting all rows and columns)

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

SQL INSERT INTO SELECT 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_NONAMEADDRESSPHONEAge
1RamDelhiXXXXXXXXXX18
2RAMESHGURGAONXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
4SURESHDelhiXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
2RAMESHGURGAONXXXXXXXXXX18
7SOUVIKDUMDUMXXXXXXXXXX18
8NIRAJNOIDAXXXXXXXXXX19
9SOMESHROHTAKXXXXXXXXXX20

Method 2(Inserting specific columns)

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

SQL INSERT INTO SELECT 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_NONAMEADDRESSPHONEAge
1RamDelhiXXXXXXXXXX18
2RAMESHGURGAONXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
4SURESHDelhiXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
2RAMESHGURGAONXXXXXXXXXX18
7SOUVIKnullnull18
8NIRAJnullnull19
9SOMESHnullnull20

Select specific rows to insert:

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_NONAMEADDRESSPHONEAge
1RamDelhiXXXXXXXXXX18
2RAMESHGURGAONXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
4SURESHDelhiXXXXXXXXXX18
3SUJITROHTAKXXXXXXXXXX20
2RAMESHGURGAONXXXXXXXXXX18
7SOUVIKDUMDUMXXXXXXXXXX18

To insert multiple rows in a table using Single SQL Statement:

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.

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:

IDNAMEAGEGRADECITY
1AMIT KUMAR1510DELHI
2GAURI RAO1812BANGALORE
3MANAV BHATT1711NEW DELHI
4RIYA KAPOOR105UDAIPUR



Last Updated : 30 Oct, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads