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
– George, Commented Oct 29, 2014 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, <tableName> is not a real destination table name, is it? — George
– George, Commented Oct 29, 2014 at 19:19
There is no error message. I am copying to an existing table in my database. And no, ha, <tableName> was just used for this posting. I'm reading more about ColumnMappings but haven't had much luck yet. — Aaron Hurst
– Aaron Hurst, Commented Oct 29, 2014 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
– George, Commented Oct 29, 2014 at 20:08

Tim · Accepted Answer · 2014-10-29 18:46:58Z

3

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.

answered Oct 29, 2014 at 18:46

Tim

4,09910 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chinor · Accepted Answer · 2014-10-31 11:09:37Z

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

Collectives™ on Stack Overflow

Using SqlBulkCopy for inserting to SQL

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related