SQL COPY TABLE

If you want to copy the data of one SQL table into another SQL table in the same SQL server, then it is possible by using the SELECT INTO statement in SQL.

The SELECT INTO statement in Structured Query Language copies the content from one existing table into the new table. SQL creates the new table by using the structure of the existing table.

Syntax of SELECT INTO statement in SQL

SELECT * INTO New_table_name FROM old_table_name;  

Examples of SELECT INTO statement in SQL

In this article, we have taken the following three different SQL examples which will help you how to copy the content of one table into another table in SQL:

Example 1: In this example, we have a table called Cars with three columns:

Car Name	Car Color	Car Cost
Hyundai Creta	White	10,85,000
Hyundai Venue	White	9,50,000
Hyundai i20	Red	9,00,000
Kia Sonet	White	10,00,000
Kia Seltos	Black	8,00,000
Swift Dezire	Red	7,95,000

Table: Cars

• Suppose you want to copy the content of the above Car table into the new table Car_Details. For this, you have to type the following query in SQL:

SELECT * INTO Car_Details FROM Cars;   

• Let’s check the Car_Details table is created successfully or not in the database:

SELECT * FROM Car_Details;   

Car Name	Car Color	Car Cost
Hyundai Creta	White	10,85,000
Hyundai Venue	White	9,50,000
Hyundai i20	Red	9,00,000
Kia Sonet	White	10,00,000
Kia Seltos	Black	8,00,000
Swift Dezire	Red	7,95,000

Table: Car_Details

Example 2: In this example, we have a table called Employee with four columns:

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	Abhay	25000	Goa
202	Ankit	45000	Delhi
203	Bheem	30000	Goa
204	Ram	29000	Goa
205	Sumit	40000	Delhi

• Suppose you want to copy the record of the above Employee table into the new table Coding_Employees. For this, you have to type the following query in SQL:

SELECT * INTO Coding_Employees FROM Employee;  

• Let’s check the Coding_Employees table is created successfully or not in the database:

SELECT * FROM Coding_Employees;  

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	Abhay	25000	Goa
202	Ankit	45000	Delhi
203	Bheem	30000	Goa
204	Ram	29000	Goa
205	Sumit	40000	Delhi

Table: Coding_Employees

Example 3: In this example, we have a table called Student with four columns:

RollNo	Name	Marks	Age
1001	Bhanu	88	17
1002	Raman	82	16
1003	Sumit	80	16
1004	Shobhit	95	15
1005	Akash	85	16

Table: Student

• Suppose you want to copy the record of the above Student table into the new table Class_12_Students. For this, you have to type the following query in SQL:

SELECT * INTO Class_12_Students FROM Student;  

• Let’s check the table is Class_12_Students table created successfully or not in the database:

SELECT * FROM Class_12_Students;  

RollNo	Name	Marks	Age
1001	Bhanu	88	17
1002	Raman	82	16
1003	Sumit	80	16
1004	Shobhit	95	15
1005	Akash	85	16

Table: Class_12_Students

Example 4: In this example, we have a table called Cars with three columns:

Car Name	Car Color	Car Cost
Hyundai Creta	White	10,85,000
Hyundai Venue	White	9,50,000
Hyundai i20	Red	9,00,000
Kia Sonet	White	10,00,000
Kia Seltos	Black	8,00,000
Swift Dezire	Red	7,95,000

Table: Cars

• Suppose you want to copy Car_Color and Car_Name columns of the above Cars table into the new table Car_Color. For this, you have to type the following query in SQL:

SELECT Car_Name, Car_Color INTO Car_Color FROM Cars;  

• Let’s check the Car_Color table is created successfully or not in the database:

SELECT * FROM Car_Color;  

Car Name	Car Color
Hyundai Creta	White
Hyundai Venue	White
Hyundai i20	Red
Kia Sonet	White
Kia Seltos	Black
Swift Dezire	Red

Table: Car_Color

Syntax of SELECT INTO statement with WHERE clause in SQL

SELECT * INTO New_table_name FROM old_table_name WHERE [ condition ] ;  

Examples of SELECT INTO statement with WHERE clause in SQL

Here, we have taken the following three different SQL examples, which will help you how to copy the content of one table into another table with a specific condition in SQL:

Example 1: In this example, we have a table called Cars with three columns:

Car Name	Car Color	Car Cost
Hyundai Creta	Black	10,85,000
Hyundai Venue	Black	9,50,000
Hyundai i20	Red	9,00,000
Kia Sonet	White	10,00,000
Kia Seltos	Black	8,00,000
Swift Dezire	Red	7,95,000

Table: Cars

• Suppose we want to copy only the record of those cars whose color is black. For this, we have to type the following query in SQL:

SELECT * INTO Black_Car_Details FROM Cars WHERE Car_Color = 'Black';  

• Let’s check the Black_Car_Details table is created successfully or not in the database:

SELECT * FROM Black_Car_Details;  

Car Name	Car Color	Car Cost
Hyundai Creta	Black	10,85,000
Hyundai Venue	Black	9,50,000
Kia Seltos	Black	8,00,000

Table: Black_Car_Details

Example 2: In this example, we have a table called Employee with four columns:

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	Abhay	45000	Goa
202	Ankit	45000	Delhi
203	Bheem	38000	Goa
204	Ram	49000	Goa
205	Sumit	40000	Delhi

Table: Employee

• Suppose we want to copy only the record of those employees whose Salary is more than 40,000. For this, we have to type the following query in SQL:

SELECT * INTO Emp_Salary_40000 FROM Cars WHERE Emp_Salary > 40000;  

• Let’s check the Emp_Salary_40000 table created successfully or not in the database:

SELECT * FROM Emp_Salary_40000;  

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	Abhay	45000	Goa
202	Ankit	45000	Delhi
204	Ram	49000	Goa

Table: Emp_Salary_40000