在实时情况下，我们确实遇到需要将数据从一个表复制到另一个表，或者需要根据表中的一组数据创建表的情况。
我们将在本文中讨论此类情况的解决方案。

SQL复制表

在我们处理敏感数据的情况下，创建备份表非常重要。
在这种情况下，从一个表复制数据并使用相同的数据创建新表非常有用。
SQL复制表是一项功能，使我们可以将数据从一个表复制到另一个表。

我们将看到如何在下面提到的三个数据库中使用"复制表"功能。

• MySQL复制表
• PostgreSQL复制表
• SQL Server复制表

1. MySQL复制表

语法：

Create table newTable

Select column(s) from existingTable;

在上述语法中，首先使用newTable名称创建一个表，然后通过select语句的结果集定义新表的结构。

让我们假设库 表为例。

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`)
)

以下查询将用于数据插入。

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表

现在，我们将尝试创建价格超过100的表格。

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

让我们检查新创建的表。

Select * from library_moreThan100

复制后的SQL新表

如果我们只想在表中插入复制的数据，则可以使用以下查询。

INSERT newTable
SELECT *
FROM existingTable;

2. PostgreSQL复制表

语法：

Create table newTable

Select column(s) from existingTable;

MySQL和PostgreSQL的语法与SQL Copy命令相同。

让我们假设库 表为例。

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")
)

以下查询将用于数据插入。

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);

现在，我们将尝试从库表创建备份表。

CREATE TABLE test.library_bk AS 
TABLE test.library;

让我们检查新创建的表。

Select * from test.library_bk;

3. SQL Server复制表

语法：

Select * into newTable from existingTable;

根据上述语法，SQL Server将创建一个名称为newTable的新表，并将使用现有表的结构。

让我们假设库 表为例。

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")
)

以下查询将用于数据插入。

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);

现在，我们将尝试从库表创建备份表。

Select * into library_bk from library;

让我们检查新创建的表。

Select * from library_bk;