how to pass variables like arrays / datatable to SQL server?

Question

Sometimes you need to upgrade the database with many rows that you have in a datatable or you have an array full of data, instead of putting all this data together in a string and then splitting in SQL SERVER, or instead of iterating the datatable in the code row by row and updating database, is there any other way? Is there other type of variables besides the traditional ones in SQL SERVER 2005?

Answer 1

I agree with John that SqlBulkCopy or sqlxml are the best options in SQL Server 2005; note that in SQL Server 2008 you also have table valued parameters, which would be worth consideration; especially when mixed with the new merge join.

Note that if you use SqlBulkCopy, I strongly recommend bulk inserting only to a staging table - i.e. a separate table just for the import; then run a stored procedure to move the data to the live table. That way you get proper logging, and you can have a tightly scoped transaction just at the database while you run the SP (i.e. you don't need a transaction spanning all that network IO).

One other point; if you are dealing with large volumes of data, you might not want to have to load a DataTable (since that forces you to buffer all the data in memory first); as an alternative, it is also possible to write your own IDataReader that pulls data from a stream (such as a file etc); see SimpleDataReader here. SqlBulkCopy will accept data from an IDataReader very happily.