Section 5.8. Bulk-Copy Rows from One Table to Another | Visual Basic 2005: A Developers Notebook

5.8. 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.

5.8.1. 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 destination tables match exactly, no mapping is required. However, if the table names differ, you need to set the SqlBulkCopy.DestinationTableName property to the name of the target table. Additionally, if the column names don't match or if there are fewer columns in the target table than there are in the source data, you also need to configure column mapping. To set column mapping, you add one mapping object for each column to the SqlBulkCopy.ColumnMappings collection. Each mapping object specifies the name of the source column and the name of the corresponding target column.

Figure 5-1. Creating a CustomersShort table

To try this out, create a new SQL Server database named NorthwindCopy and a table named CustomersShort. The CustomersShort table is designed to offer a subset of the information in the Customers table. You can create it by using a tool like SQL Server Enterprise Manager (see the column settings in Figure 5-1), or you can use the script included with the downloadable content for this chapter to create it automatically (look for the file GenerateNorthwindCopy.sql).

Once you've created CustomersShort, you have a perfect table for testing a SQL Server bulk-copy operation. All you need to do is create two connections, define the mapping, and start the process. Example 5-8 has the code you need.

Example 5-8. Using SQLBulkCopy

Imports System.Data.SqlClient      Module Module1          Private ConnectSource As String = _      "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"     Private ConnectTarget As String = _      "Data Source=localhost;Initial Catalog=NorthwindCopy;" &_          Public Sub Main( )         ' Create the source and target connections.         Dim conSource As New SqlConnection(ConnectSource)         Dim conTarget As New SqlConnection(ConnectTarget)              ' Create a command for counting the number of rows in a table.         Dim cmdCount As New SqlCommand("SELECT COUNT(*) FROM CustomersShort", _           conTarget)              ' Initialize the SqlBulkCopy class with mapping information.         Dim BCP As New SqlClient.SqlBulkCopy(conTarget)         BCP.DestinationTableName = "CustomersShort"         BCP.ColumnMappings.Add("CustomerID", "ID")         BCP.ColumnMappings.Add("CompanyName", "Company")         BCP.ColumnMappings.Add("ContactName", "Contact")              ' Count the rows in CustomersShort.         conTarget.Open( )         Dim Rows As Integer = CInt(cmdCount.ExecuteScalar( ))         Console.WriteLine("CustomersShort has " & Rows & " rows.")         Console.WriteLine("Starting bulk copy...")              ' Retrieve the rows you want to transfer.         conSource.Open( )         Dim cmd As New SqlCommand( _           "SELECT CustomerID,CompanyName,ContactName FROM Customers", conSource)         Dim reader As SqlDataReader = cmd.ExecuteReader( )              ' Write the data to the destination table.         BCP.WriteToServer(reader)              ' Clean up.         BCP.Close( )         reader.Close( )         conSource.Close( )              ' Count the rows in CustomersShort again.         conSource.Open( )         Rows = CInt(cmdCount.ExecuteScalar( ))         Console.WriteLine("Finished bulk copy.")         Console.WriteLine("CustomersShort has " & Rows & " rows.")              conTarget.Close( )         Console.ReadLine( )     End Sub      End Module

When you run the code, you'll see output like this, indicating that the bulk-copy operation completed successfully:

CustomersShort has 0 rows. Starting bulk copy... Finished bulk copy. CustomersShort has 91 rows.

5.8.2. What about...

...other SqlBulkCopy properties? SqlBulkCopy provides two useful properties: BulkCopyTimeout (which allows you to set how long you'll wait for an unresponsive server) and BatchSize (which allows you to set how many operations are batched together, as described in the lab "Batch DataAdapter Commands for Better Performance"). Errors are handled in the same way as when you directly execute a SqlCommand. In other words, if an error happens on the server side (like a unique value conflict), the process will be interrupted immediately, and you'll receive a SqlClient exception with the full details.

5.8.3. Where can I learn more?

For a complete list of class members, look up the SqlBulkCopy class in the MSDN help library reference. Or, for information about the original BCP utility, look for the index entry "bcp utility" in the SQL Server Books Online help.