Visual Basic 2005: A Developer's Notebook

Bulk-Copy Rows from One Table to Another

Most SQL Server gurus are familiar with the BCP command-line utility, which allows you to move vast amounts of information from one SQL Server database to another. BCP comes in handy any time you need to load a large number of records at once, but it's particularly useful when you need to transfer data between servers. In .NET 2.0, the SqlClient namespace includes a new SqlBulkCopy class that allows you to perform a bulk-copy operation programmatically.

Note

The new SqlBulkCopy class gives you the most efficient way to copy large amounts of data between tables or databases.

How do I do that?

The key ingredient in a bulk-copy operation is the new SqlBulkCopy class. It performs all of its work when you call the WriteToServer() method, which can be used in two ways:

• You can submit your data as a DataTable or an array of DataRow objects. This makes sense if you want to insert a batch of records from a file you created earlier. It also works well if you're creating a server-side component (like a web service) that receives a disconnected DataSet with the records that need to be loaded into a table.

• You can submit your data as an open DataReader that draws records from another SqlConnection. This approach is ideal if you want to transfer records from one database server to another.

Before you call WriteToServer( ), you need to create the connections and commands you need and set up mapping between the destination and source table. If your source and ...