从一个数据库表复制到另一个 C#
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/565732/
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 from one database table to another C#
提问by Brettski
Using C# (vs2005) I need to copy a table from one database to another. Both database engines are SQL Server 2005. For the remote database, the source, I only have execute access to a stored procedure to get the data I need to bring locally.
使用 C# (vs2005) 我需要将一个表从一个数据库复制到另一个数据库。两个数据库引擎都是SQL Server 2005。对于远程数据库，源，我只有执行访问一个存储过程来获取我需要带到本地的数据。
The local database I have more control over as it's used by the [asp.net] application which needs a local copy of this remote table. We would like it local for easier lookup and joins with other tables, etc.
我对本地数据库有更多控制权，因为它被 [asp.net] 应用程序使用，该应用程序需要此远程表的本地副本。我们希望它是本地的，以便于查找和连接其他表等。
Could you please explain to me an efficient method of copying this data to our local database.
您能否向我解释一种将这些数据复制到我们本地数据库的有效方法。
The local table can be created with the same schema as the remote one, if it makes things simpler. The remote table has 9 columns, none of which are identity columns. There are approximately 5400 rows in the remote table, and this number grows by about 200 a year. So not a quickly changing table.
本地表可以使用与远程表相同的模式创建，如果它使事情更简单的话。远程表有 9 列，其中没有一个是标识列。远程表中大约有 5400 行，并且这个数字每年增长大约 200。所以不是一个快速变化的桌子。
采纳答案by Canavar
Bulk Copy feature of ADO.NET might help you take a look at that :
ADO.NET 的批量复制功能可能会帮助您了解：
回答by Richard
I would first look at using SQL Server Intergration Services (SSIS, née Data Transfer Services (DTS)).
我首先会考虑使用 SQL Server 集成服务（SSIS，née 数据传输服务 (DTS)）。
It is designed for moving/comparing/processing/transforming data between databases, and IIRC allows an arbitrary expression for the source. You would need it installed on your database (shouldn't be a problem, it is part of a default install).
它是为在数据库之间移动/比较/处理/转换数据而设计的，IIRC 允许对源进行任意表达式。您需要将它安装在您的数据库上（应该没有问题，它是默认安装的一部分）。
Otherwise a code solution, given the data size (small), pull all the data from the remove system into an internal structure, and then look for rows which don't exist locally to insert.
否则，代码解决方案，给定数据大小（小），将删除系统中的所有数据拉入内部结构，然后查找本地不存在的行进行插入。
回答by Marc Gravell
Perhaps SqlBulkCopy; use SqlCommand.ExecuteReader to get the reader that you use in the call to SqlBulkCopy.WriteToServer. This is the same as bulk-insert, so very quick. It should look somethinglike (untested);
也许 SqlBulkCopy；使用 SqlCommand.ExecuteReader 获取您在调用 SqlBulkCopy.WriteToServer 时使用的读取器。这与批量插入相同，因此非常快。它应该看起来什么样（未经测试）;
using (SqlConnection connSource = new SqlConnection(csSource))
using (SqlCommand cmd = connSource.CreateCommand())
using (SqlBulkCopy bcp = new SqlBulkCopy(csDest))
{
bcp.DestinationTableName = "SomeTable";
cmd.CommandText = "myproc";
cmd.CommandType = CommandType.StoredProcedure;
connSource.Open();
using(SqlDataReader reader = cmd.ExecuteReader())
{
bcp.WriteToServer(reader);
}
}
回答by Canavar
You probably can't do this, but if you can't, DON'T do it with a program. If you have any way of talking to someone who controls the source server, see if they will set up some sort of export of the data. If the data is as small as you say, then xml or csv output would be 100x better than writing something in c# (or any language).
您可能无法做到这一点，但如果您做不到，请不要使用程序来做到这一点。如果您有任何方式与控制源服务器的人交谈，请查看他们是否会设置某种形式的数据导出。如果数据像你说的一样小，那么 xml 或 csv 输出将比用 c#（或任何语言）编写东西好 100 倍。
So let's assume they can't export, still, avoid writing a program. You say you have more control over the destination. Can you set up an SSIS package, or setup a linked server? If so, you'll have a much easier time migrating the data.
所以让我们假设他们不能导出，仍然避免编写程序。你说你对目的地有更多的控制权。您可以设置 SSIS 包或设置链接服务器吗？如果是这样，您将可以更轻松地迁移数据。
If you set up at bare minimum the source as a linked server you could write a small t-sql batch to
如果您至少将源设置为链接服务器，您可以编写一个小的 t-sql 批处理
TRUNCATE DestTable
截断目标表
INSERT INTO DestTable SELECT SourceTable.Star FROM [SourceServer].[Schema].[Table]
INSERT INTO DestTable SELECT SourceTable.Star FROM [SourceServer].[Schema].[Table]
wouldn't be as nice as SSIS (you have more visual of what's happening, but the t-sql above is pretty clear).
不会像 SSIS 那样好（您对正在发生的事情有更多的了解，但是上面的 t-sql 非常清楚）。
Since I would not take the programming route, the best solution I could give you would be, if you absolutely had to:
由于我不会走编程路线，如果您绝对必须这样做，我可以给您的最佳解决方案是：
Use SqlClient namespace.
使用 SqlClient 命名空间。
So, create 2 SqlConnections, 2 SqlCommands, and get the instance of the 1 SqlReader.
因此，创建 2 个 SqlConnections、2 个 SqlCommands，并获取 1 个 SqlReader 的实例。
Iterate through the source reader, and execute the destination SqlCommand insert for each iteration with the.
遍历源读取器，并为每次迭代执行目标 SqlCommand 插入。
It'll be ugly, but it'll work.
这会很丑，但它会起作用。
回答by Dan
Doesn't seem to be huge quantity of data you have to synchronize. Under conditions you described (only SP to access the remote DB and no way to get anything else), you can go for Marc Gravell's solution. In the case the data can only grow and existing data can not be changed you can compare the record count on remote and internal DB in order to optimize operation; if no change in remote DB no need to copy.
您必须同步的数据量似乎并不大。在您描述的条件下（只有 SP 可以访问远程数据库，而无法获得其他任何东西），您可以使用 Marc Gravell 的解决方案。在数据只能增长而现有数据不能改变的情况下，您可以比较远程和内部数据库的记录数以优化操作；如果远程数据库没有变化，则无需复制。
