快速將A資料表的資料匯到B資料表
前陣子一位同學分享給我處裡匯入大量資料到資料表的作法，這個做法有相當好的效能，可快速將A資料表的資料匯到B資料表。
我稍作調整，可自行設定要匯入的資料欄位。
Public Sub SqlBulkCopy(ByRef SourceSqlstring As String, ByRef DestinationTableName As String, ByRef SourceItemList As List(Of String), ByRef DestinationItemList As List(Of String))
Using sourceConnection As System.Data.SqlClient.SqlConnection = _
New System.Data.SqlClient.SqlConnection(SqlConnectionString)
sourceConnection.Open()
Dim commandSourceData As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(SourceSqlstring, sourceConnection)
Dim reader As System.Data.SqlClient.SqlDataReader = commandSourceData.ExecuteReader
Using destinationConnection As System.Data.SqlClient.SqlConnection = _
New System.Data.SqlClient.SqlConnection(SqlConnectionString)
destinationConnection.Open()
Using bulkCopy As System.Data.SqlClient.SqlBulkCopy = _
New System.Data.SqlClient.SqlBulkCopy(destinationConnection)
bulkCopy.DestinationTableName = DestinationTableName
For i As Integer = 0 To SourceItemList.Count - 1
For x As Integer = 0 To DestinationItemList.Count - 1
bulkCopy.ColumnMappings.Add(SourceItemList.Item(i).ToString(), DestinationItemList.Item(x).ToString())
Next
Next
Try
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Throw ex
Finally
reader.Close()
End Try
End Using
End Using
End Using
