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 SELECT

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

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

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

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_IdTourist_NameTourist_CountryGender
101PrakashIndiaM
102AmolIndiaM
103GeniferAustraliaF
104MeghannAustraliaF
105TembaSouth AfricaM
106DaneSouth AfricaF
107OdeanWest IndiesM
108RossNewZealandM
109HayleyWest IndiesF
110SophieNewZealandF

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

TpidHistoryKilometerStateTpname
11Beauty160KarnatakaBeluru
12Monuments270KeralaKochi
13Beach360TamilNaduMarina
14History300KarnatakaChikmagalur

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_IdTourist_NameTourist_CountryGender
101PrakashIndiaM
102AmolIndiaM
103GeniferAustraliaF
104MeghannAustraliaF
105TembaSouth AfricaM
106DaneSouth AfricaF
107OdeanWest IndiesM
108RossNewZealandM
109HayleyWest IndiesF
110SophieNewZealandF
SQL COPY TABLE

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;
TpidHistoryKilometerStateTpname
11Beauty160KarnatakaBeluru
12Monuments270KeralaKochi
13Beach360TamilNaduMarina
14History300KarnatakaChikmagalur
SQL COPY TABLE

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_IdTourist_NameTourist_CountryGender
101PrakashIndiaM
102AmolIndiaM
103GeniferAustraliaF
104MeghannAustraliaF
105TembaSouth AfricaM
106DaneSouth AfricaF
107OdeanWest IndiesM
108RossNewZealandM
109HayleyWest IndiesF
110SophieNewZealandF
SQL COPY TABLE

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;
TpidHistoryKilometerStateTpname
11Beauty160KarnatakaBeluru
12Monuments270KeralaKochi
13Beach360TamilNaduMarina
14History300KarnatakaChikmagalur
SQL COPY TABLE



ADVERTISEMENT
ADVERTISEMENT