SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL what-are-single-row-and-multiple-row-subqueries">What are single row and multiple row subqueries?

SQL INSERT INTO SELECT

In this tutorial, we will help you to understand and learn how to copy records from one table and add them to another table in the SQL with the help of examples.

The INSERT INTO SELECT query is used to add records from one table to another using the SELECT statement. In other words, we can say this statement copies data from one table and adds it to another.

Before using the INSERT INTO SELECT query, we shouldn't forget the key points as follows:

1 The data we will add to the table must have existed in the database from the table we will copy.

2 The data types must be the same as the source and target table.

The syntax of the INSERT INTO SELECT statement is as follows:

INSERT INTO Table_Name_1 (SELECT * FROM Table_Name_2 WHERE condition);

In the above syntax, the where a condition is optional. The WHERE clause is used to add selected data from one table to another.

Use the below syntax to add records into selected columns using the INSERT INTO SELECT query is as follows:

INSERT INTO Table_Name_2 (Column_Name1, Column_Name2, Column_Name3, Column_Name4) SELECT Column_Name1, Column_Name2, Column_Name3, Column_Name4 FROM Table_Name_2 WHERE Condition;

Let’s understand how to use the INSERT INTO SELECT statement with the help of an examples.

First, we will create a new table name Student_information:

CREATE TABLE Student_Information (Studn_info_Id int not null, Student_Name varchar(40) not null, Student_gender varchar(1) not null, Student_age int not null, Marks int not null, Degree varchar(40) not null, Primary key(Studn_info_Id));

Let’s add some data into the Student_information table:

INSERT INTO Student_Information values(1, 'Priya Chaudhary', 'F', 23, 560, 'BE');
INSERT INTO Student_Information values(2, 'Utkarsh Kulkarni', 'M', 23, 550, 'B.Tech');
INSERT INTO Student_Information values(3, 'Rakhi Jain', 'F', 22, 580, 'M.COM');
INSERT INTO Student_Information values(4, 'Nikita Ingale', 'F', 23, 620, 'BE');
INSERT INTO Student_Information values(5, 'Piyush Narkhede', 'M', 22, 600, 'BSC');
INSERT INTO Student_Information values(6, 'Pawan Sharma', 'M', 24, 590, 'B.COM');
INSERT INTO Student_Information values(7, 'Tushar Mahalle', 'M', 22, 680, 'B.Tech');
INSERT INTO Student_Information values(8, 'Sakashi Sharma', 'F', 21, 650, 'BSC');
INSERT INTO Student_Information values(9, 'Gaurav Gupta', 'M', 22, 635, 'BSC');
INSERT INTO Student_Information values(10, 'Manish Kapoor', 'M', 23, 500, 'MCOM');

We will execute the select query to display the records in the table form as follows:

SELECT * FROM Student_Information;
Studn_info_IdStudent_NameStudent_GenderStudent_ageMarksDegree
1Priya ChaudharyF23560BE
2Utkarsh KulkarniM23550B.Tech
3Rakhi JainF22580MCOM
4Nikita IngaleF23620BE
5Piyush NarkhedeM22600BSC
6Pawan SharmaM24590B.COM
7Tushar MahalleM22680B.Tech
8Sakashi SharmaF21650BSC
9Gaurav GuptaM22635B.COM
10Manish KapoorM23500MCOM

Now, we will create one more table named Student_Info which is our target table in this example.

CREATE TABLE Student_Info (Stud_info_Id int not null, Student_Name varchar(40) not null, Student_gender varchar(1) not null, Student_age int not null, Marks int not null, Degree varchar(40) not null, Primary key(Stud_info_Id));

Now, we will have another table name Student_Infos, which will be our second target table in this example.

CREATE TABLE Student_Infos (Stud_info_Id int not null, Student_Name varchar(40) not null, Student_gender varchar(1) not null, Student_age int not null, Marks int not null, Degree varchar(40) not null, Primary key(Stud_info_Id));

Example 1: Write a query to add all the records from the student_information table to the Student_info table using the INSERT INTO SELECT query.

INSERT INTO Student_Info (SELECT * FROM Student_Information);

In the above INSERT INTO SELECT query example, we copied the entire student_information table data into the Student_Info table.

We will execute the SELECT query on the Student_Info table to verify whether the data is successfully added or not as follows:

SELECT * FROM Student_Info;

The output of the above query is as follows:

Studn_info_IdStudent_NameStudent_GenderStudent_ageMarksDegree
1Priya ChaudharyF23560BE
2Utkarsh KulkarniM23550B.Tech
3Rakhi JainF22580MCOM
4Nikita IngaleF23620BE
5Piyush NarkhedeM22600BSC
6Pawan SharmaM24590B.COM
7Tushar MahalleM22680B.Tech
8Sakashi SharmaF21650BSC
9Gaurav GuptaM22635B.COM
10Manish KapoorM23500MCOM
SQL INSERT INTO SELECT

Example 2: Write a query to add all the records from the student_information table to the Student_infos table but only for selected columns using the INSERT INTO SELECT query.

INSERT INTO Student_Infos (Stud_Info_Id, Student_Name, Student_age, Marks, Degree) (SELECT Studn_Info_Id, Student_Name, Student_age, Marks, Degree FROM Student_Information); 

In the above INSERT INTO SELECT query example, we copied the selected column records from the student_information table data into the Student_Infos table.

We will execute the SELECT query on the Student_Info table to verify whether the data is successfully added or not as follows:

SELECT * FROM Student_Infos;

The output of the above query is as follows:

Studn_info_IdStudent_NameStudent_GenderStudent_ageMarksDegree
1Priya Chaudhary 23560BE
2Utkarsh Kulkarni 23550B.Tech
3Rakhi Jain 22580MCOM
4Nikita Ingale 23620BE
5Piyush Narkhede 22600BSC
6Pawan Sharma 24590B.COM
7Tushar Mahalle 22680B.Tech
8Sakashi Sharma 21650BSC
9Gaurav Gupta 22635B.COM
10Manish Kapoor 23500MCOM
SQL INSERT INTO SELECT

Now, we will have another table name Student_Infos, which will be our third target table in this example.

CREATE TABLE Students_Info (Stud_info_Id int not null, Student_Name varchar(40) not null, Student_gender varchar(1) not null, Student_age int not null, Marks int not null, Degree varchar(40) not null, Primary key(Stud_info_Id));

Example 3: Write a query to add the records from the student_information table to the Students_info table where student gender is ‘M’ using the INSERT INTO SELECT query.

INSERT INTO Students_Info (SELECT * FROM Student_Information WHERE Student_Gender = ‘M’);

In the above INSERT INTO SELECT query example, we copied the data from the student_information table where student gender is ‘M’ into the Students_Info table.

We will execute the SELECT query on the Students_Info table to verify whether the data is successfully added or not as follows:

SELECT * FROM Students_Info;

The output of the above query is as follows:

Studn_info_IdStudent_NameStudent_GenderStudent_ageMarksDegree
2Utkarsh KulkarniM23550B.Tech
5Piyush NarkhedeM22600BSC
6Pawan SharmaM24590B.COM
7Tushar MahalleM22680B.Tech
9Gaurav GuptaM22635B.COM
10Manish KapoorM23500MCOM
SQL INSERT INTO SELECT