0

I've written a program in VB (although I'm equally comfortable with C#, so please answer in either language) that is used to insert a large amount of data into a table in my SQL server DB.

The way I've planned on doing it is:

  1. To get the table's Schema from the DB using the SqlDataAdapter.FillSchema method.
  2. Add the many rows of data to my DataTable
  3. Use SqlBulkCopy (.WriteToServer(dtToCopy.CreateDataReader)) to load in the data

I've done this many times before and it's proven very efficient. My problem is that the table I'm writing to has an auto-incrementing primary key column, RowNum.

Now I know I could re-query my table after getting the schema and get the latest value of RowNum and simply write in the values myself into the corresponding column in my DataTable, but my fear is that the table may change between the time of my query and the time of my write, in which case, I could have un-desirable results.

I've seen posts relating to MissingSchemaAction > AddWithKey, but I'm not sure exactly how I could implement it in my case?

Would I just remove the primary key and delete the RowNum column from my DataTable? And, if so, at what point do I set that property? It doesn't seem to be a property of the DataTable and I don't see where to add it in the SqlBulkCopy.

Thanks!!!

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • Does your DataTable currently have that RowNum column, and if so, with a "default" value or with "usable" values? – Bernhard Hiller Jul 14 '14 at 14:56
  • @BernhardHiller, it gets the column from the Database with the `FillSchema` command, but it doesn't get the seed value... – John Bustos Jul 14 '14 at 15:11
  • Creating a data table for large amount of data is inefficient. Suppose you have 10 million records, you don't wanna load it all into memory. You may want to consider using a data reader instead: http://msdn.microsoft.com/en-us/library/434atets(v=vs.110).aspx – Victor Zakharov Jul 14 '14 at 15:52
  • 1
    Thanks, @Neolisk, I also have that procedure. For this specific example, though, I liked the DataTable since I also perform Linq queries against it and use it in my code elsewhere, but a very useful and logical suggestion. Thanks! – John Bustos Jul 14 '14 at 16:04
  • 1
    You can use Linq-To-Sql for that. – Victor Zakharov Jul 14 '14 at 16:10
  • Makes sense... Gonna try and implement it... Thanks @Neolisk – John Bustos Jul 14 '14 at 16:42

1 Answers1

1

After a lot of searching, I found the solution myself.

Basically, I had to:

  1. Set the PrimaryKey property of my DataTable to Nothing
  2. Remove the RowNum column from my DataTable
  3. Use ColumnMappings for my SqlBulkCopy so every other column is mapped and the identity is left out

Then, since it is a primary key and set as auto-increment in the DB, it gets automatically assigned the correct values at the time of load.

Hope this helps others!!!

Main Source: SqlBulkCopy Insert with Identity Column

Community
  • 1
  • 1
John Bustos
  • 19,036
  • 17
  • 89
  • 151