SQL 将包含数据的表从一种模式复制到另一种模式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13740572/
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
Copy table(s) with data from one schema to another?
提问by mezamorphic
I have a number of tables with data in Schema1and I would like to copy these tables, with the data, to a new schema I have created, Schema2.
我有许多包含数据的表，Schema1我想将这些表连同数据一起复制到我创建的新模式中Schema2。
Is there an elegant way of doing this? I am using SQL Server Management studio.
有没有一种优雅的方式来做到这一点？我正在使用 SQL Server 管理工作室。
回答by LarsHJ
In SQL Management studio right click the database that has the source table, select Tasks -> Export data.
在 SQL Management Studio 中右键单击包含源表的数据库，选择任务 -> 导出数据。
You will be able to set source and destination server and schema, select the tables you wish to copy and you can have the destination schema create the tables that will be exported.
您将能够设置源和目标服务器和模式，选择要复制的表，并且可以让目标模式创建将导出的表。
Also, if copying to the same server and database but different schema be sure to:
此外，如果复制到相同的服务器和数据库但架构不同，请确保：
- Use the Sql Server Native Client (see
https://i.stack.imgur.com/Qqhbd.png) for Source and Destination
parameters - Select the same database name for the Source and Destination parameters
- Choose copy data from one or more tables or views (optional) In the Select Source Tables and ViewsGUI
- Change the destination table's schema to something different than the source schema (i.e. type something like "newschema.tablename")
- 使用 Sql Server Native Client（参见
https://i.stack.imgur.com/Qqhbd.png）获取源和目标
参数 - 为 Source 和 Destination 参数选择相同的数据库名称
- 在Select Source Tables and ViewsGUI 中从一个或多个表或视图中选择复制数据（可选）
- 将目标表的模式更改为与源模式不同的内容（即键入类似“newschema.tablename”的内容）
回答by PeterJ
Assuming the schema has structural changes, so you can't just do a backup and restore to a new database you can right-mouse click on the database and select tasks | generate scripts. On the second page there is an option (off by default) to script the data.
假设架构有结构变化，所以你不能只做备份和恢复到一个新的数据库，你可以右键单击数据库并选择任务 | 生成脚本。在第二页上有一个选项（默认关闭）来编写数据脚本。
That creates a series of SQL insert statements along with the CREATE TABLE statements. You may also want to select Script Triggers and check a few of the other options that you may require if using those features.
这将创建一系列 SQL 插入语句以及 CREATE TABLE 语句。如果使用这些功能，您可能还需要选择脚本触发器并检查一些您可能需要的其他选项。
You can also do an INSERT across schemas or use SSIS as described here:
您还可以跨架构执行 INSERT 或使用 SSIS，如下所述：
How can I copy data records between two instances of an SQLServer database
如何在 SQLServer 数据库的两个实例之间复制数据记录
One thing to be aware of is that I believe the export data facility doesn't propagate the indexes, constraints, triggers etc which is why I like to use the first method for moderate amounts of data or at least to create the new tables in the first place followed by one of the other methods.
需要注意的一件事是，我相信导出数据工具不会传播索引、约束、触发器等，这就是为什么我喜欢对中等数量的数据使用第一种方法，或者至少在第一名，然后是其他方法之一。
回答by Michael Potter
Here is what I did:
这是我所做的：
- right click on source table and select "select top 1000 rows".
- remove "top 1000" from select.
- add "INTO newschema.newtable" above the from clause
- run that query.
- create the indexes on the new table.
- 右键单击源表并选择“选择前 1000 行”。
- 从选择中删除“前 1000 名”。
- 在 from 子句上方添加“INTO newschema.newtable”
- 运行该查询。
- 在新表上创建索引。
This will not work for everyone because it requires the new schema to be available from the same connection in SQL Studio.
这并不适用于所有人，因为它要求新架构可从 SQL Studio 中的同一连接使用。
