将表从一个数据库复制到另一个数据库

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命令来实现这一点?我希望创建新表，从旧表复制数据并将其插入到新表中。

9 个解决方案

#1

Create a linked server to the source server. The easiest way is to right click "Linked Servers" in Management Studio; it's under Management -> Server Objects.

创建一个链接服务器到源服务器。最简单的方法是在Management Studio中右键单击“链接服务器”;它在管理之下——>服务器对象。

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.

这将创建具有与原始表相同结构的新表，并复制数据。

#2

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 Export和Import wizard，它在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/

请阅读本文中关于这个方法的更多内容，以及更多的方法(包括生成脚本、选择INTO和第三方工具):https://www.sqlshack.com/howto -copy-tables-from-one database-to- other in- server/

#3

SELECT ... INTO :

选择……成:

select * into <destination table> from <source table>

#4

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.TableName to SecondDB.TableName and won't create foreign keys or indexes.

这将创建一个新表，并从FirstDatabase复制数据。表名SecondDB。TableName不会创建外键或索引。

#5

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!

希望它可以帮助!

#6

INSERT INTO ProductPurchaseOrderItems_bkp
      (
       [OrderId],
       [ProductId],
       [Quantity],
       [Price]
      )
SELECT 
       [OrderId],
       [ProductId],
       [Quantity],
       [Price] 
FROM ProductPurchaseOrderItems 
   WHERE OrderId=415

#7

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!

当我从localhost移动到webhost时，这对我来说是可行的。希望它对你有用!

#8

The quickest way is to use a tool, like RazorSQL or Toad for doing this.

最快的方法是使用一个工具，比如RazorSQL或Toad。

#9

If you need the entire table structure (not just the basic data layout), use Task>Script Table As>Create To>New Query Window and run in your new database. Then you can copy the data at your leisure.

如果您需要整个表结构(不只是基本的数据布局)，请使用Task>脚本表作为>创建到>的新查询窗口，并在新的数据库中运行。然后你可以在空闲时间复制数据。

#1