SQL Copy Table

Filed Under: SQL

In real time we do face situations where we need to copy data from one table to another table or when we need to create a table from a set of data from the table. We will discuss in this article the solution for such cases.

SQL Copy Table

Creating a backup table is very important in situations where we are dealing with sensitive data. In such situation copying data from one table and creating a new table using the same data is very useful. SQL Copy table is a feature that allows us to perform the copying of data from one table to another.

We will see how Copy table feature can be used in below mentioned three databases.

  1. MySQL Copy Table
  2. PostgreSQL Copy Table
  3. SQL Server Copy Table

1. MySQL Copy Table

Syntax:- 

Create table newTable

Select column(s) from existingTable;

In the above syntax, first, a table is created with the newTable name then the structure of the new table is defined by the result set of the select statement.

Let us assume the Library table for example.

CREATE TABLE `library` (
`idLibrary` int(11) NOT NULL,
`BookTitle` varchar(45) DEFAULT NULL,
`BookQuantity` int(11) DEFAULT NULL,
`Author` varchar(45) DEFAULT NULL,
`BookPrice` float DEFAULT NULL,
PRIMARY KEY (`idLibrary`),
UNIQUE KEY `idLibrary_UNIQUE` (`idLibrary`)
)

The below-mentioned query will be used for data insertion.

INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(1,'The Chamber of Secrets',10,'J K Rowling',20.99);
INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(2,'One night at the call center',13,'Chetan Bhagat',100.99);
INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(3,'The God of Small things',11,'Arundhati Roy',120.99);
INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(4,'War and Peace',5,'Leo Tolstoy',80.00);
SQL Table Before Copy

SQL Table Before Copy

Now we will try to create a table with a price of more than 100.

Create table library_moreThan100
Select * from library where bookprice>100;

Let us check the newly created table.

Select * from library_moreThan100
SQL Copy Table

SQL New Table After Copy

If we want to only insert copied data in a table then the following query can be used.

INSERT newTable
SELECT *
FROM existingTable;

2. PostgreSQL Copy Table

Syntax:- 

Create table newTable

Select column(s) from existingTable;

The syntax for MySQL and PostgreSQL is the same for SQL Copy command.

Let us assume the Library table for example.

CREATE TABLE "library" (
  "idLibrary" int NOT NULL,
  "BookTitle" varchar(45) DEFAULT NULL,
  "BookQuantity" int DEFAULT NULL,
  "Author" varchar(45) DEFAULT NULL,
  "BookPrice" float DEFAULT NULL,
  PRIMARY KEY ("idLibrary"),
  Constraint "idLibrary_UNIQUE"  UNIQUE ("idLibrary")
)

The below-mentioned query will be used for data insertion.

INSERT INTO "test"."library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(1,'The Chamber of Secrets',10,'J K Rowling',20.99);
INSERT INTO "test"."library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(2,'One night at the call center',13,'Chetan Bhagat',100.99);
INSERT INTO "test"."library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(3,'The God of Small things',11,'Arundhati Roy',120.99);
INSERT INTO "test"."library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(4,'War and Peace',5,'Leo Tolstoy',80.00);
PostgreSQL Table Before Copy

PostgreSQL Table Before Copy

Now we will try to create a backup table from the library table.

CREATE TABLE test.library_bk AS 
TABLE test.library;

Let us check the newly created table.

Select * from test.library_bk;
PostgreSQL New Table After Copy

PostgreSQL New Table After Copy

3. SQL Server Copy Table

Syntax:- 

Select * into newTable from existingTable;

Based on the syntax above, SQL server will create a new table with the name as newTable and will use the structure of the existingTable.

Let us assume the Library table for example.

CREATE TABLE "library" (
  "idLibrary" int NOT NULL,
  "BookTitle" varchar(45) DEFAULT NULL,
  "BookQuantity" int DEFAULT NULL,
  "Author" varchar(45) DEFAULT NULL,
  "BookPrice" float DEFAULT NULL,
  PRIMARY KEY ("idLibrary"),
  Constraint "idLibrary_UNIQUE"  UNIQUE ("idLibrary")
)

The below-mentioned query will be used for data insertion.

INSERT INTO "library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(1,'The Chamber of Secrets',10,'J K Rowling',20.99);
INSERT INTO "library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(2,'One night at the call center',13,'Chetan Bhagat',100.99);
INSERT INTO "library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(3,'The God of Small things',11,'Arundhati Roy',120.99);
INSERT INTO "library"("idLibrary","BookTitle","BookQuantity","Author","BookPrice")VALUES(4,'War and Peace',5,'Leo Tolstoy',80.00);
SQL Server Table Before Copy

SQL Server Table Before Copy

Now we will try to create a backup table from the library table.

Select * into library_bk from library;

Let us check the newly created table.

Select * from library_bk;
SQL Server New Table After Copy

SQL Server New Table After Copy

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages