Using SqlBulkCopy for inserting to SQL

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

Is there an error message? Does a source query result in any records? This shouldn't matter, but just in case try adding `bulkcopy.BatchSize = 500` and `bulkcopy.BulkCopyTimeout=2400` before `bulkcopy.WriteToServer(reader)`. The batchsize optimizes the run, but shouldn't matter much in this case, you can play around with values to see which number works best for u. `bulkCopy.BulkCopyTimeout` may actually help if you are experiencing timeouts. — George, Oct 29 '14 at 18:34
Are you Bulk Copying to a new table or already existing one? If a new table, does it actually exist? I don't believe bulk copy will create a table if it does not exist. If already existing one, then follow Tim's suggestion. I don;t see any particular error in your logic, your code is actually very close to mine, and I did not use `ColumnMappings` while bulk-copying to a temp table. PS: just to rule our something silly and obvious, `` is not a real destination table name, is it? — George, Oct 29 '14 at 19:19
There is no error message. I am copying to an existing table in my database. And no, ha, was just used for this posting. I'm reading more about ColumnMappings but haven't had much luck yet. — Aaron Hurst, Oct 29 '14 at 20:04
Test this by copying to a brand new table. Create this table using source sql like this `SELECT TOP 0 * INTO NewTable FROM YourInputSelectTable`. This will copy the field structure into the new table and copy the data. In theory, you won't need ColumnMappings for this task. — George, Oct 29 '14 at 20:08

score 3 · Accepted Answer · answered Oct 29 '14 at 18:46

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.

score 1 · Answer 2 · answered Oct 31 '14 at 11:09

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

Using SqlBulkCopy for inserting to SQL

2 Answers2