SQL Server - 创建数据库表的副本并将其放在同一个数据库中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15428168/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - Create a copy of a database table and place it in the same database?
提问by sequel.learner
I have a table ABC in a database DB. I want to create copies of ABC with names ABC_1, ABC_2, ABC_3 in the same DB. How can I do that using either Management Studio (preferably) or SQL queries ?
我在数据库 DB 中有一个表 ABC。我想在同一个数据库中创建名称为 ABC_1、ABC_2、ABC_3 的 ABC 副本。我如何使用 Management Studio（最好）或 SQL 查询来做到这一点？
This is for SQL Server 2008 R2.
这适用于 SQL Server 2008 R2。
回答by Mahmoud Gamal
Use SELECT ... INTO:
使用SELECT ... INTO：
SELECT *
INTO ABC_1
FROM ABC;
This will create a new table ABC_1that has the same column structure as ABCand contains the same data. Constraints (e.g. keys, default values), however, are -not- copied.
这将创建一个ABC_1与列结构相同ABC并包含相同数据的新表。但是，约束（例如键、默认值）不会被复制。
You can run this query multiple times with a different table name each time.
您可以每次使用不同的表名多次运行此查询。
If you don't need to copy the data, only to create a new empty table with the same column structure, add a WHEREclause with a falsy expression:
如果不需要复制数据，只需要新建一个列结构相同的空表，添加一个WHERE带有falsy表达式的子句：
SELECT *
INTO ABC_1
FROM ABC
WHERE 1 <> 1;
回答by JohnLBevan
Copy Schema (Generate DDL) through SSMS UI
通过 SSMS UI 复制架构（生成 DDL）
In SSMS expand your database in Object Explorer, go to Tables, right click on the table you're interested in and select Script Table As, Create To, New Query Editor Window.
Do a find and replace (CTRL+ H) to change the table name (i.e. put ABCin the Find Whatfield and ABC_1in the Replace Withthen click OK).
在 SSMS 中，在Object Explorer 中展开您的数据库，转到Tables，右键单击您感兴趣的表并选择Script Table As、Create To、New Query Editor Window。执行查找和替换（CTRL+ H）以更改表名（即放入ABC“查找内容”字段和ABC_1“替换为”，然后单击“确定”）。
Copy Schema through T-SQL
通过 T-SQL 复制架构
The other answers showing how to do this by SQL also work well, but the difference with this method is you'll also get any indexes, constraints and triggers.
显示如何通过 SQL 执行此操作的其他答案也很有效，但与此方法的不同之处在于您还将获得任何索引、约束和触发器。
Copy Data
复制数据
If you want to include data, after creating this table run the below script to copy all data from ABC (keeping the same ID values if you have an identity field):
如果要包含数据，请在创建此表后运行以下脚本以从 ABC 复制所有数据（如果您有标识字段，则保持相同的 ID 值）：
set identity_insert ABC_1 on
insert into ABC_1 (column1, column2) select column1, column2 from ABC
set identity_insert ABC_1 off
回答by Rousonur Jaman
If you want to duplicate the table with all its constraints & keys follows this below steps:
如果要复制包含所有约束和键的表，请按照以下步骤操作：
- Open the database in SQL Management Studio.
- Right-click on the table that you want to duplicate.
- Select Script Table as -> Create to -> New Query Editor Window. This will generate a script to recreate the table in a new query window.
- Change the table name and relative keys & constraints in the script.
- Execute the script.
- 在 SQL Management Studio 中打开数据库。
- 右键单击要复制的表。
- 选择 Script Table as -> Create to -> New Query Editor Window。这将生成一个脚本以在新查询窗口中重新创建表。
- 更改脚本中的表名和相关键和约束。
- 执行脚本。
Then for copying the data run this below script:
然后复制数据运行以下脚本：
SET IDENTITY_INSERT DuplicateTable ON
INSERT Into DuplicateTable ([Column1], [Column2], [Column3], [Column4],... )
SELECT [Column1], [Column2], [Column3], [Column4],... FROM MainTable
SET IDENTITY_INSERT DuplicateTable OFF
回答by Claude
1st option
第一个选项
select *
into ABC_1
from ABC;
2nd option: use SSIS, that is right click on database in object explorer > all tasks > export data
第二个选项：使用 SSIS，即右键单击对象资源管理器中的数据库 > 所有任务 > 导出数据
- source and target: your DB
- source table: ABC
- target table: ABC_1 (table will be created)
- 源和目标：您的数据库
- 源表：ABC
- 目标表：ABC_1（将创建表）
回答by Keni
This is another option:
这是另一种选择：
select top 0 * into <new_table> from <original_table>
回答by Chris D
You need to write SSIS to copy the table and its data, constraints and triggers. We have in our organization a software called Kal Admin by kalrom Systems that has a free version for downloading (I think that the copy tables feature is optional)
您需要编写 SSIS 来复制表及其数据、约束和触发器。我们在我们的组织中有一个由 kalrom Systems 提供的名为 Kal Admin 的软件，它有一个免费版本可供下载（我认为复制表功能是可选的）
回答by gasroot
use sql server manegement studio or netcat and that will be easier to manipulate sql
使用 sql server manegement studio 或 netcat，这样操作 sql 会更容易
