SQL 将表从一个数据库复制到另一个数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8285256/
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 from one database to another
提问by Baz
I've just created an empty database on my machine. I now wish to copy a table from our server database to this local database.
我刚刚在我的机器上创建了一个空数据库。我现在希望将一个表从我们的服务器数据库复制到这个本地数据库。
What sql commands do I need to run to do this? I wish to create the new table, copy data from the old table and insert it into the new table.
我需要运行哪些 sql 命令才能执行此操作？我希望创建新表，从旧表中复制数据并将其插入新表中。
回答by Andomar
Create a linked serverto the source server. The easiest way is to right click "Linked Servers" in Management Studio; it's under Management -> Server Objects.
创建到源服务器的链接服务器。最简单的方法是在 Management Studio 中右键单击“Linked Servers”；它在管理-> 服务器对象下。
Then you can copy the table using a 4-part name, server.database.schema.table:
然后您可以使用由 4 部分组成的名称复制表server.database.schema.table：
select *
into DbName.dbo.NewTable
from LinkedServer.DbName.dbo.OldTable
This will both create the new table with the same structure as the original one and copy the data over.
这将创建与原始表具有相同结构的新表并复制数据。
回答by Mahmoud Gamal
Assuming that they are in the same server, try this:
假设他们在同一台服务器上，试试这个：
SELECT *
INTO SecondDB.TableName
FROM FirstDatabase.TableName
This will create a new table and just copy the data from FirstDatabase.TableNameto SecondDB.TableNameand won't create foreign keys or indexes.
这将创建一个新表，并且只是从FirstDatabase.TableNameto复制数据，SecondDB.TableName不会创建外键或索引。
回答by D.Blosch
Another method that can be used to copy tables from the source database to the destination one is the SQL Server Export and Import wizard, which is available in SQL Server Management Studio.
另一种可用于将表从源数据库复制到目标数据库的方法是 SQL Server 导出和导入向导，该向导在 SQL Server Management Studio 中可用。
You have the choice to export from the source database or import from the destination one in order to transfer the data.
您可以选择从源数据库导出或从目标数据库导入以传输数据。
This method is a quick way to copy tables from the source database to the destination one, if you arrange to copy tables having no concern with the tables' relationships and orders.
如果您安排复制表而不考虑表的关系和顺序，则此方法是将表从源数据库复制到目标数据库的一种快速方法。
When using this method, the tables' indexes and keys will not be transferred. If you are interested in copying it, you need to generate scripts for these database objects.
使用此方法时，不会传输表的索引和键。如果您有兴趣复制它，则需要为这些数据库对象生成脚本。
If these are Foreign Keys, connecting these tables together, you need to export the data in the correct order, otherwise the export wizard will fail.
如果这些是外键，将这些表连接在一起，需要按照正确的顺序导出数据，否则导出向导会失败。
Feel free to read more about this method, as well as about some more methods (including generate scripts, SELECT INTO and third party tools) in this article: https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/
请随意阅读本文中有关此方法以及更多方法（包括生成脚本、SELECT INTO 和第三方工具）的更多信息：https: //www.sqlshack.com/how-to-copy-tables-从一个数据库到另一个 in-sql-server/
回答by aleroot
SELECT ... INTO :
选择...进入：
select * into <destination table> from <source table>
回答by skpaik
INSERT INTO ProductPurchaseOrderItems_bkp
(
[OrderId],
[ProductId],
[Quantity],
[Price]
)
SELECT
[OrderId],
[ProductId],
[Quantity],
[Price]
FROM ProductPurchaseOrderItems
WHERE OrderId=415
回答by alejandrob
If migrating constantly between two databases, then insert into an already existing table structure is a possibility. If so, then:
如果在两个数据库之间不断迁移，则可以插入到已经存在的表结构中。如果是这样，那么：
Use the following syntax:
使用以下语法：
insert into DESTINATION_DB.dbo.destination_table
select *
from SOURCE_DB.dbo.source_table
[where x ...]
If migrating a LOT of tables (or tables with foreign keys constraints) I'd recommend:
如果迁移大量表（或带有外键约束的表），我建议：
- Generating Scripts with the Advanced option / Types of data to script : Data only OR
- Resort using a third party tool.
- 使用高级选项生成脚本/要编写脚本的数据类型：仅数据或
- 度假村使用第三方工具。
Hope it helps!
希望能帮助到你！
回答by ZyteX
Assuming that you want different names for the tables.
假设您需要不同的表名称。
If you are using PHPmyadmin you can use their SQL option in the menu. Then you simply copy the SQL-code from the first table and paste it into the new table.
如果您使用 PHPmyadmin，您可以在菜单中使用他们的 SQL 选项。然后您只需从第一个表中复制 SQL 代码并将其粘贴到新表中。
That worked out for me when I was moving from localhost to a webhost. Hope it works for you!
当我从本地主机迁移到网络主机时，这对我有用。希望这对你有用！
回答by dav
If you need the entire table structure (not just the basic data layout), use Task>Script Table As>Create To>New Query Windowand run in your new database. Then you can copy the data at your leisure.
如果您需要整个表结构（不仅仅是基本数据布局），请Task>Script Table As>Create To>New Query Window在新数据库中使用和运行。然后您可以随意复制数据。
回答by Alpha01
The quickest way is to use a tool, like RazorSQL or Toad for doing this.
最快的方法是使用工具，如 RazorSQL 或 Toad 来执行此操作。
