从一个表复制数据，并将其保存到不同SQL服务器上不同数据库中的另一个表中

I have two different databases in two different SQL Servers. The databases are identical in schema but contain different data in one of the tables.

我在两个不同的SQL服务器中有两个不同的数据库。模式中的数据库是相同的，但在其中一个表中包含不同的数据。

I want to copy all the data from one table in one database to the same table in the other database so that I can get rid of the database from which I am copying the data.

我想要将一个数据库中的一个表中的所有数据复制到另一个数据库中的同一个表中，这样我就可以删除正在复制数据的数据库。

The data is too large so I cannot create data scripts and run it onto other database.

数据太大，所以我无法创建数据脚本并将其运行到其他数据库。

How can I achieve this?

我如何做到这一点?

2 个解决方案

#1

There are many ways like ssis transfer,select * into ,but i prefer below way if you are just transferring data

有很多方法，比如ssis transfer,select * into，但是如果您只是传输数据，我更喜欢下面的方法

create a linked server on source server for destination server,then you could refer destination server with four part name

在源服务器上为目标服务器创建一个链接服务器，然后您可以使用四个部件名称来引用目标服务器。

Assuming linked server of source is A and destination server is B,data moving is as simple as

假设源的链接服务器是A，目标服务器是B，那么数据移动就简单到

insert into B.databasename.Schema.Table
select * from table---this is in source server and db

if data is huge and you may worry about time outs,you can write a simple script which can do in batches like

如果数据很大，您可能会担心超时，那么可以编写一个简单的脚本，它可以批量执行

 While (1=1)
    begin
    insert into B.databasename.Schema.Table
    select  top 10000* from table---this is in source server and db
    if (@@rowcount=0)
    break
    end

Creating linked server ,you can follow this

创建链接服务器，您可以遵循这一点

#2

You have the following options available to you. Not all of these will work, depending on your exact requirements and the networking arrangements between the servers.

您可以使用以下选项。并不是所有这些都可以工作，这取决于您的确切需求和服务器之间的网络安排。

SQL Server Management Studio - Import & Export Wizard: this is accessed from the right-click menu for a database > Tasks > Import Data (or Export Data).

SQL Server Management Studio - Import & Export向导:通过右键单击菜单访问数据库>任务>导入数据(或导出数据)。
SQL query using a Linked Server: a Linked Server configured between the two servers allows you to reference databases on one from the other, in much the same way as if they were on the same server. Any valid SQL query approach for transferring data between two tables within one database will then work, provided you fully-qualify the table names as Server.Database.Schema.Table.

使用链接服务器的SQL查询:在两个服务器之间配置的链接服务器允许您从一个服务器引用另一个服务器上的数据库，其方式与它们在同一服务器上的方式非常相似。如果您将表名完全限定为Server.Database.Schema.Table，那么用于在一个数据库中的两个表之间传输数据的任何有效SQL查询方法都将正常工作。
SSIS: create an SSIS package with both servers as connections, and a simple workflow to move the data from one to the other. There is plenty of information available online on how to use SSIS.

SSIS:创建一个包含两个服务器的SSIS包作为连接，一个简单的工作流将数据从一个移动到另一个。网上有很多关于如何使用SSIS的信息。
Export to flat-file format then import: this could be done using the Import/Export Wizard above or SSIS, but instead of piping the data directly between the two servers, you would output the data from the source table into a suitable flat-file format on the filesystem. CSV is the most commonly used format for this. This file can then be moved to the destination server using any file transfer approach (compressed e.g. to a Zip file if desired), and imported into the destination table.

导出到平面文件格式，然后导入:这可以使用上面的导入/导出向导或SSIS来完成，但是不需要直接在两个服务器之间传输数据，您可以将源表中的数据输出到文件系统上合适的平面文件格式。CSV是最常用的格式。然后可以使用任何文件传输方法(如压缩到Zip文件)将该文件移动到目标服务器，并导入目标表。
Database backup and restore: Similar to (4), but instead of using a flat file, you could create a backup of the source database via Tasks > Back Up... You then move that backup as a file (just like the CSV approach), and restore it onto the destination server. Now you have two databases on the destination server, and can move data from one to the other locally.

数据库备份和恢复:类似于(4)，但是不使用平面文件，您可以通过任务>备份创建源数据库的备份……然后将该备份作为文件移动(就像CSV方法一样)，并将其恢复到目标服务器。现在，目标服务器上有两个数据库，可以在本地将数据从一个数据库移动到另一个数据库。

#1