使用SqlBulkCopy插入SQL

Question

我不知道为什么数据没有进入SQL。有人能看看我的代码出了什么问题吗？我从MSDN页面获得了以下大部分代码：http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy（v=vs.110）.aspx

Dim connectionString As String = "Server= "<servername>"; integrated security=true"
    Using sourceConnection As SqlConnection = _
        New SqlConnection(connectionString)
        sourceConnection.Open()
        Dim commandSourceData As SqlCommand = New SqlCommand(<TSQL>), sourceConnection)
        Dim reader As SqlDataReader = commandSourceData.ExecuteReader
        Using destinationConnection As SqlConnection = _
            New SqlConnection(connectionString)
            destinationConnection.Open()
            Using bulkcopy As SqlBulkCopy = _
                New SqlBulkCopy(destinationConnection)
                bulkcopy.DestinationTableName = _
                    "<tableName>"
                Try
                    bulkcopy.WriteToServer(reader)
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                Finally
                    reader.Close()
                End Try
            End Using
        End Using
        sourceConnection.Close()
    End Using

Answer 1

在我执行BulkCopy的系统中，我按照文档column Mapping-SQL中的说明设置列映射批量复制

映射是用源列名和目标列名设置的。此示例来自文档：

 Dim mapID As New _
              SqlBulkCopyColumnMapping("ProductID", "ProdID")
            bulkCopy.ColumnMappings.Add(mapID)

当我第一次设置时，我记得在我的环境中没有显式设置列映射时遇到了问题。

Answer 2

如果数据源的字段类型、字段顺序、字段计数与目标表不相同。您的散货船将无法返回。

在上述情况下，您应该将数据仓库中每个字段的ColumnMappings指定为sqlbulkcopy对象。

            SqlBulkCopy copy = new SqlBulkCopy(MySqlConn);
            copy.BulkCopyTimeout = 6000;
            copy.DestinationTableName = TableName;
            for (int i = 0; i < this.lstBulkFields.Count; i++) {
                // if the source fields name are the same with the targets.
                copy.ColumnMappings.Add(this.lstBulkFields[i], this.lstBulkFields[i]);
            }
            copy.BatchSize = BatchSize;
            copy.WriteToServer(MyDataSource);