SqlBulkInsert with a DataTable to a Linked Server

我在不同的机器上使用 2 台 SQL 2008 服务器。服务器名称为 source.ex.com 和 destination.ex.com 。

destination.ex.com链接到source.ex.com并且具有适当的权限，以便source.ex.com写入名为 bacon-wrench on destination.ex.com 的数据库

我已经通过短信登录source.ex.com并测试了此查询（成功）：

INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
(PunchID, BaconID) VALUES (4,6);

在 C# .NET 4.0 网页中，我连接到source.ex.com并执行类似的查询（成功）：

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
    c.Open();
    String sql = @"
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
        (PunchID, BaconID) VALUES (34,56);";
    using(SqlCommand cmd = new SqlCommand(sql, c))
    {
        cmd.ExecuteNonQuery();
    }
}

对于一小组插入语句（例如 20 或更少），执行这样的事情可以正常工作：

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
    c.Open();
    String sql = @"
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
         (PunchID, BaconID) VALUES (34,56);
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
         (PunchID, BaconID) VALUES (22,11);
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
         (PunchID, BaconID) VALUES (33,55);
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
         (PunchID, BaconID) VALUES (1,2);";
    using(SqlCommand cmd = new SqlCommand(sql, c))
    {
        cmd.ExecuteNonQuery();
    }
}

我正在尝试用大约 20000 条记录做这样的事情。上面的方法需要 11 分钟才能完成 - 我假设这是服务器在向我发出恶意，使其成为某种批量操作。从其他 StackOverflow 线程中，推荐使用 SqlBulkCopy 类，它作为参数DataTable，完美！

所以我构建了一个数据表并尝试将其写入服务器（失败）：

DataTable dt = new DataTable();
dt.Columns.Add("PunchID", typeof(int));
dt.Columns.Add("BaconID", typeof(int));
for(int i = 0; i < 20000; i++)
{
    //I realize this would make 20000 duplicate 
    //rows but its not important 
    dt.Rows.Add(new object[] {
        11, 33 
    });
}
using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
    c.Open();
    using(SqlBulkCopy bulk = new SqlBulkCopy(c))
    {
        bulk.DestinationTableName = "[destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]";
        bulk.ColumnMappings.Add("PunchID", "PunchID");
        bulk.ColumnMappings.Add("BaconID", "BaconID");
        bulk.WriteToServer(dt);
    }
}

编辑2：以下消息是我试图解决的消息：

网页在bulk.WriteToServer(dt);崩溃，并显示错误消息Database bacon-wrench does not exist please ensure it is typed correctly.我做错了什么？如何更改此设置以使其正常工作？

编辑1：

我能够使用以下语法显着加快查询速度。但对于这么小的记录集来说，它仍然很慢。

using(SqlConnection c = new SqlConnection(ConfigurationManager.ConnectionStrings["SOURCE"].ConnectionString))
{
    c.Open();
    String sql = @"
        INSERT INTO [destination.ex.com].[bacon-wrench].[dbo].[tblFruitPunch]
         (PunchID, BaconID) VALUES 
         (34,56),
         (22,11),
         (33,55),
         (1,2);";
    using(SqlCommand cmd = new SqlCommand(sql, c))
    {
        cmd.ExecuteNonQuery();
    }
}

if(TYPE_ID(N'[Owner].[TempTableType]') is null)
begin
CREATE TYPE [Owner].[TempTableType] AS TABLE ( [PendingID] uniqueidentifier, [Reject] bit)
end
IF  NOT EXISTS (SELECT * FROM [LinkedServer].[DatabaseOnLS].sys.tables where name = 'TableToReceive') 
EXEC('
CREATE TABLE [DatabaseOnLS].[Owner].[TableToReceive] ( [PendingID] uniqueidentifier, [Reject] bit)
') AT [LinkedServer]
else
EXEC('
TRUNCATE TABLE [DatabaseOnLS].[Owner].[TableToReceive]
') AT [LinkedServer]
CREATE PROCEDURE [Owner].[TempInsertTable]
@newTableType TempTableType readonly
AS
BEGIN
insert into [LinkedServer].[DatabaseOnLS].[Owner].[TableToReceive] select * from @newTableType 
END

using (var command = new SqlCommand("TempInsertTable",
                    oUoW.Database.Connection as SqlConnection) { CommandType = CommandType.StoredProcedure }
                )
{
    command.Transaction = oUoW.Database.CurrentTransaction as SqlTransaction;
    command.Parameters.Add(new SqlParameter("@newTableType", oTempTable));
    drResults = command.ExecuteReader();
    drResults.Close();
}