We come across situations when we have to copy data from one table to the other table. This is usually accomplished by using SQL Queries. Visual Studio.NET 2005 Beta 2 introduces a SqlBulkCopy class that can perform the same operation efficiently. In this article we will take a look at the features provided by SqlBulkCopy class.

 

Introduction:

We come across situations when we have to copy data from one table to the other table. This is usually accomplished by using SQL  Queries. Visual Studio.NET 2005 Beta 2 introduces a SqlBulkCopy class that can perform the same operation efficiently. In this article we will take a look at the features provided by SqlBulkCopy class.

Copy data from one table to another using SQL Query:

You can simply copy the data from one table to the other table using SQL Query given below.

CREATE TABLE Person3 ( PersonID int IDENTITY(1,1) PRIMARY KEY, Name nvarchar(200), Email nvarchar(200), Picture image ) INSERT INTO Person3(Name,Email,Picture) SELECT Name,Email,Picture FROM Person

For this query we also assumed that our target table Person3 was not created and so we created it. Although this will work fine but you may sometimes need to perform the same operation programmatically using C# code.

Copy data from one table to another using SqlBulkCopy class: 

The primary purpose of the SqlBulkCopy class is to copy data from one source to other. Let's see how this can be done.

string connectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString; SqlConnection myConnection = new SqlConnection(connectionString); SqlCommand myCommand = new SqlCommand("SELECT * FROM Person", myConnection); myConnection.Open(); SqlDataReader dr = myCommand.ExecuteReader(); SqlConnection myNewConnection = new SqlConnection(connectionString); myNewConnection.Open(); SqlBulkCopy bulk = new SqlBulkCopy(myNewConnection); bulk.DestinationTableName = "[Person3]"; try { bulk.WriteToServer(dr); } catch (Exception ex) { Response.Write(ex.Message); } finally { myNewConnection.Close(); dr.Close(); myConnection.Close(); bulk.Close(); }

First, we are using the ConfigurationManager class to extract the connection string out of the web.config file. Than we make a command object which selects everything from the Person table. Person table is also our source table which means data is copied from this table to the destination table. Finally we open the connection and execute the DataReader on the command object. This was the first half of the story.

Now let's see the second half. We open a new connection myNewConnection because one reader can only be associated with one open connection. We make an instance of the SqlBulkCopy class and send myNewConnection as a parameter to the constructor. Than we set the property DestinationTableName to the table where the data will be copied. 

bulk.DestinationTableName = "[Person3]";

And finally we run bulk.WriteToServer(dr) method to write the data to the destination table.

In this example we did not define any column mappings because the column names and number of columns in both tables were identical. In the next example we will see that how we can map the corresponding columns if the column names don't match.

First create a new table whose column names are different than the source table.

CREATE TABLE Person2 ( PersonID int IDENTITY(1,1) PRIMARY KEY, FirstName nvarchar(200), LastName nvarchar(200), Email nvarchar(200), Picture image )

Our new table Person2 has different column mapping than the source table Person. Let's see what will happen if we try to use the same approach as we did for Person3 table. If you run the above code and just replace the bulk.DestinationTableName with [Person2] than you will see the following error.

The locale id '0' of the source column 'Picture' and the locale id '1033' of the destination column 'Email' do not match.

This should not be a surprise for us since the column in both the tables did not match. Let's see how we can create column mappings.

string connectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString; SqlConnection myConnection = new SqlConnection(connectionString); SqlCommand myCommand = new SqlCommand("SELECT * FROM Person", myConnection); myConnection.Open(); SqlDataReader dr = myCommand.ExecuteReader(); SqlConnection myNewConnection = new SqlConnection(connectionString); myNewConnection.Open(); SqlBulkCopy bulk = new SqlBulkCopy(myNewConnection); bulk.DestinationTableName = "[Person2]"; bulk.ColumnMappings.Add("Name", "LastName"); bulk.ColumnMappings.Add("Email", "Email"); bulk.ColumnMappings.Add("Picture", "Picture"); try { bulk.WriteToServer(dr); } catch (Exception ex) { Response.Write(ex.Message); } finally { myNewConnection.Close(); dr.Close(); myConnection.Close(); bulk.Close(); }

Now if you run this it will insert the rows in Person2 table and NULL will be inserted in all the columns that did not match. Also you need to keep in mind that SqlBulkCopy will append the rows at the bottom of the table if you are continuously adding the rows in a loop.

Update: You must be wondering that instead of writing all those lines of code you can simply write a stored procedure that can copy one table to the other. Offcourse you can do that but remember that when the number of records in the table grows the performance of your stored procedure or SQL query will be very poor. According to analysis SqlBulkCopy class provides the same speed as DTS.

I hope you like the article, happy coding !