Load data using the SqlBulkCopy class : SqlBulkCopy « Database ADO.net « VB.Net Tutorial

1. VB.Net Tutorial
2. Database ADO.net
3. SqlBulkCopy

Imports System.Data.SqlClient

Module Module1
    Sub Main()
        Using sourceConnection As SqlConnection = New SqlConnection("Data Source=(local);Integrated Security=true;Initial Catalog=AdventureWorks;")
            sourceConnection.Open()
            Dim commandRowCount As New SqlCommand("SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",sourceConnection)
            Dim countStart As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
            Console.WriteLine("Starting row count = {0}", countStart)

            Dim commandSourceData As SqlCommand = New SqlCommand( _
               "SELECT ProductID, Name, ProductNumber " & _
               "FROM Production.Product;", sourceConnection)
            Dim reader As SqlDataReader = commandSourceData.ExecuteReader

            Using destinationConnection As SqlConnection = New SqlConnection("Data Source=(local);Integrated Security=true;Initial Catalog=AdventureWorks;")
                destinationConnection.Open()
                Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(destinationConnection)
                    bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
                    Try
                        bulkCopy.WriteToServer(reader)
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    Finally
                        reader.Close()
                    End Try
                End Using
                Dim countEnd As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
                Console.WriteLine("Ending row count = {0}", countEnd)
                Console.WriteLine("{0} rows were added.", countEnd - countStart)
            End Using
        End Using
    End Sub
End Module