I'm reading data from an Excel file or CSV. I take that data and create a datatable. I then merge that datatable with the datatable created from the original database. The merge works and I have already sorted out all of the datatypes and column names. I have a ton of links, but most of them boil down to datatype and column name/column text case.
No errors. Everything runs smoothly. The datatable I'm trying to bulk copy is correct in the VS table viewer. When I check in SQLExpress no changes have been made. I am using the same connection string that I have been for the rest of the project which works (row delete, add, edit, etc).
dt.Merge(dtnew) Using destinationConnection As SqlConnection = _ New SqlConnection(sConnectionString) destinationConnection.Open() ' Set up the bulk copy object. ' The column positions in the source data reader ' match the column positions in the destination table, ' so there is no need to map columns. Using bulkCopy As SqlBulkCopy = _ New SqlBulkCopy(destinationConnection) bulkCopy.DestinationTableName = _ "dbo.TableName" Try ' Write from the source to the destination. bulkCopy.WriteToServer(dt) Catch ex As Exception Console.WriteLine(ex.Message) Finally ' Close the SqlDataReader. The SqlBulkCopy ' object is automatically closed at the end ' of the Using block. End Try End Using End Using End Sub
Do the column mapping also..
bulkCopy.ColumnMappings.Add("source column name,"destination column name" )
or if you have same column name in dt and dbo.Tablename then you can use following code
For Each clmn As DataColumn In dt.Columns bulkCopy.ColumnMappings.Add(clmn.ColumnName, clmn.ColumnName) Next