Just started a Data Migration project that involves moving 12 tables with
about 100mb of data from one SQL Server to another. For various reasons, the
dba won't let us use DTS so we're using ADO.NET to pull the data down. The
arguement is now between manually generating a bcp file to upload into the
destination server, or to create a datatable and call
DataAdapter.Update(DataTable). I personally prefer doing the DataTable way,
since all the code is already there and we don't ahve to manually generate a
bcp file. BUT the dba is 90% against allowing the application to view the
tables directly, they have to all go through storedprocs (I say 90% because
he is open to us to coding it *both* ways and then if there's a
*significant* performance over bcp, we can go with direct-access).

So, anyone know of good datamigration articles I can use for or against the
various positions?

-BKN

With large data volumes, bulk insert methods like BCP are an order of
magnitude faster than individual insert statements or proc calls.
DataAdapter.Update essentially issues a SqlCommand for each row in the
underlying DataTable so performance will be much slower than a bulk insert
technique.

In .NET 2.0, you can bulk insert directly from a DataTable using the
SqlBulkCopy class.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Bryce K. Nielsen" <bryce (AT) sysonyx (DOT) com> wrote