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