Using SqlBulkCopy for inserting to SQL

Question

Using SqlBulkCopy for inserting to SQL

sqlbulkcopy sql-server vb.net

Question

I don't know why data isn't getting into SQL. Can someone please review this and see what's wrong with my code? I got most of the code below from and MSDN page: 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

Aaron Hurst

Popular Answer

If the field-types, field-orders, fields count of your datasource, are not the same with the destination table. Your bulkcopy will return fail.

In above case, you should specificate the ColumnMappings of each fields in your datasoruce to your sqlbulkcopy object.

            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);

chinor

View more on Stack Overflow

Tim · Accepted Answer · 10/29/2014 6:46:58 PM

In the system where I do a BulkCopy, I set up the column mappings as explained in the documentation Column Mapping - SQL Bulk Copy

The mapping is set up with the source and destination column names. This example is from the documentation:

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

When I first set this up, I remember having trouble without explicitly setting up the column mappings in my environment.

Using SqlBulkCopy for inserting to SQL

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator