SQL COPY Table
In this tutorial, we will learn how to copy tables in SQL with the help of examples.
Using the SELECT INTO statement in the SQL we can copy one table into the other in the Structured Query Language.
The SELECT INTO Statement query is used to copy the entire table or selected data from the existing table to the newly created table in the SQL.
There are two ways to copy tables in the SQL:
1 First, create the new table and then use the select into the statement to copy data from the existing table.
2 Use the create table aliasing the existing table select statement.
Let's use learn both the ways to copy the table in the SQL with the help of examples.
1. SELECT INTO Statement
In the SELECT INTO statement, we have to create a new table. Then we have to use the SELECT INTO statement to copy the data from the existing table into the newly created table.
The Syntax of the SELECT INTO Statement is as follows:
SELECT * INTO New_Table FROM Old_Table;
Example of SELECT INTO statement in the SQL
Let's create the Tourist table and add 10 records to the tourist table.
Create Table Tourist (Tourist_Id int not null, Tourist_Name varchar(40) not null, Tourist_Country varchar(40) not null, Gender varchar(1) not null, primary key(Tourist_Id));
Let's add 10 records to the table:
INSERT INTO Tourist Values(101, 'Prakash', 'India', 'M');
INSERT INTO Tourist Values(102, 'Amol', 'India', 'M');
INSERT INTO Tourist Values(103, 'Genifer', 'Australia', 'F');
INSERT INTO Tourist Values(104, 'Meghann', 'Australia', 'F');
INSERT INTO Tourist Values(105, 'Temba', 'South Africa', 'M');
INSERT INTO Tourist Values(106, 'Dane', 'South Africa', 'F’);
INSERT INTO Tourist Values(107, 'Odean', 'West Indies', 'M');
INSERT INTO Tourist Values(108, 'Ross', 'NewZealand', 'M');
INSERT INTO Tourist Values(109, 'Hayley', 'West Indies', 'F');
INSERT INTO Tourist Values(110,'Sophie', 'NewZealand', 'F');
Table 1: Tourist
| Tourist_Id | Tourist_Name | Tourist_Country | Gender |
| 101 | Prakash | India | M |
| 102 | Amol | India | M |
| 103 | Genifer | Australia | F |
| 104 | Meghann | Australia | F |
| 105 | Temba | South Africa | M |
| 106 | Dane | South Africa | F |
| 107 | Odean | West Indies | M |
| 108 | Ross | NewZealand | M |
| 109 | Hayley | West Indies | F |
| 110 | Sophie | NewZealand | F |
Let’s create other table the Tourist_Place table and add 4 records in the tourist table.
Create Table Tourist_Place (Tpid integer(20), History varchar(30), Kilometer integer(30), State varchar(15), Tpname varchar(30) );
Let’s add 4 records in the table:
INSERT INTO Tourist_Place values(11,'beauty',160,'karnataka','beluru');
INSERT INTO Tourist_Place values(12,'monuments',270,'kerala','kochi');
INSERT INTO Tourist_Place values(13,'beach',360,'tamilnadu','marina');
INSERT INTO Tourist_Place values(14,'history',300,'karnataka','chikmagalur');
Table 2: Tourist_Place
| Tpid | History | Kilometer | State | Tpname |
| 11 | Beauty | 160 | Karnataka | Beluru |
| 12 | Monuments | 270 | Kerala | Kochi |
| 13 | Beach | 360 | TamilNadu | Marina |
| 14 | History | 300 | Karnataka | Chikmagalur |
Example 1: Write a query to copy the Tourist table data into the new table Tourists.
SELECT * INTO Tourists FROM Tourist;
In the above example, we copied the entire tourist table into the new table Tourists.
We will execute the select query on the Tourists table to check whether the table is successfully created and data is copied into the table or not.
SELECT * FROM Tourists;
| Tourist_Id | Tourist_Name | Tourist_Country | Gender |
| 101 | Prakash | India | M |
| 102 | Amol | India | M |
| 103 | Genifer | Australia | F |
| 104 | Meghann | Australia | F |
| 105 | Temba | South Africa | M |
| 106 | Dane | South Africa | F |
| 107 | Odean | West Indies | M |
| 108 | Ross | NewZealand | M |
| 109 | Hayley | West Indies | F |
| 110 | Sophie | NewZealand | F |
Example 2: Write a query to copy the Tourist_Place table data into the new table Tourists_Place.
SELECT * INTO Tourists_Place FROM Tourist_Place;
In the above example, we copied the entire tourists_place table into the new table Tourists_Place.
We will execute the select query on the Tourists_Place table to check whether the table is successfully created and data is copied into the table or not.
SELECT * FROM Tourists_Place;
| Tpid | History | Kilometer | State | Tpname |
| 11 | Beauty | 160 | Karnataka | Beluru |
| 12 | Monuments | 270 | Kerala | Kochi |
| 13 | Beach | 360 | TamilNadu | Marina |
| 14 | History | 300 | Karnataka | Chikmagalur |
2. CREATE A TABLE using AS
We will use the same table we used in the above examples.
Example 1: Write a query to copy tourists' table data into the newly created table Tour using the below query.
CREATE TABLE Tour AS SELECT * FROM Tourists;
In the above query, we copied the tourist's table record in the Tour table.
We will execute the select query on the Tour table to check whether the table is successfully created and data is copied into the table or not.
SELECT * FROM Tour;
| Tourist_Id | Tourist_Name | Tourist_Country | Gender |
| 101 | Prakash | India | M |
| 102 | Amol | India | M |
| 103 | Genifer | Australia | F |
| 104 | Meghann | Australia | F |
| 105 | Temba | South Africa | M |
| 106 | Dane | South Africa | F |
| 107 | Odean | West Indies | M |
| 108 | Ross | NewZealand | M |
| 109 | Hayley | West Indies | F |
| 110 | Sophie | NewZealand | F |
Example 2: Write a query to copy the Tourist_Places table data into the new table TP.
CREATE TABLE TP AS SELECT * FROM Tourists_Place;
In the above example, we copied the entire tourists_places table into the new table TP.
We will execute the select query on the TP table to check whether the table is successfully created and data is copied into the table or not.
SELECT * FROM TP;
| Tpid | History | Kilometer | State | Tpname |
| 11 | Beauty | 160 | Karnataka | Beluru |
| 12 | Monuments | 270 | Kerala | Kochi |
| 13 | Beach | 360 | TamilNadu | Marina |
| 14 | History | 300 | Karnataka | Chikmagalur |
