使用另一个表中的数据创建 SQL 表

Create SQL table with the data from another table

提问by Renuka

How do I create a table using data which is already present in another table (copy of table)?

如何使用已存在于另一个表（表的副本）中的数据创建表？

回答by OMG Ponies

The most portable means of copying a table is to:

复制表的最便携方法是：

1. Create the new table with a CREATE TABLE statement

2. Use INSERT based on a SELECT from the old table:

   INSERT INTO new_table
   SELECT * FROM old_table
   

1. 使用 CREATE TABLE 语句创建新表

2. 根据旧表中的 SELECT 使用 INSERT：

   INSERT INTO new_table
   SELECT * FROM old_table
   

In SQL Server, I'd use the INTO syntax:

在 SQL Server 中，我会使用 INTO 语法：

SELECT *
    INTO new_table
  FROM old_table

...because in SQL Server, the INTO clause creates a table that doesn't already exist.

...因为在 SQL Server 中，INTO 子句创建了一个尚不存在的表。

回答by Daniel Vassallo

If you are using MySQL, you may want to use the CREATE TABLE ... AS SELECTsyntax to create a table defined by the columns and data types of another result set:

如果您使用 MySQL，您可能希望使用以下CREATE TABLE ... AS SELECT语法创建由另一个结果集的列和数据类型定义的表：

CREATE TABLE new_table AS
SELECT * 
FROM   old_table;

Example:

例子：

CREATE TABLE old_table (id int, value int);

INSERT INTO old_table VALUES (1, 100), (2, 200), (3, 300), (4, 400);

CREATE TABLE new_table AS
SELECT * 
FROM   old_table;


SELECT * FROM new_table;
+------+-------+
| id   | value |
+------+-------+
|    1 |   100 |
|    2 |   200 |
|    3 |   300 |
|    4 |   400 |
+------+-------+
4 rows in set (0.00 sec)


DESCRIBE new_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| value | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.03 sec)

For other DBMSes, that do not support this syntax, you may want to check out @OMG Ponies' answerfor a more portable solution.

对于不支持此语法的其他 DBMS，您可能需要查看@OMG Ponies 的答案以获得更便携的解决方案。

回答by Martin Smith

For SQL Server

对于 SQL Server

SELECT * 
INTO NewTable
FROM OldTable

回答by anil_g

For Sql server:

对于 Sql 服务器：

Create new table from existing table :

从现有表创建新表：

CREATE TABLE new_table_name AS 
SELECT [col1,col2,...coln] FROM existing_table_name [WHERE condition];

Insert values into existing table form another existing table using Select command :

使用Select命令将值从另一个现有表插入现有表：

SELECT * INTO destination_table FROM source_table [WHERE conditions];

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
[ WHERE condition ];

Insert values into existing table form another existing table using Insert command :

使用插入命令从另一个现有表中插入值到现有表中：

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1,column2, column3, ... FROM table1 [WHERE condition];

回答by anil_g

if u want exact schema of existing table to new table and all values of existing table want to be inserted into your new table then execute below two queries :

如果您希望现有表的确切架构到新表，并且现有表的所有值都想插入到新表中，则执行以下两个查询：

create table new_table_name like Old_table_name;

select * into new_table_name from Old_table_name;

LIKEworks only for base tables, not for views.

LIKE仅适用于基表，不适用于视图。

If the original table is a TEMPORARY table, CREATE TABLE ... LIKE does not preserve TEMPORARY. To create a TEMPORARY destination table, use CREATE TEMPORARY TABLE ... LIKE.

如果原始表是 TEMPORARY 表，则 CREATE TABLE ... LIKE 不会保留 TEMPORARY。要创建临时目标表，请使用 CREATE TEMPORARY TABLE ... LIKE。

for more details click here

欲了解更多详情，请点击此处