Bulk copy using SqlBulkCopy object

Problem:

What if you need to copy the huge rows of data from one table to another efficiently? Simple way comes in mind is to repeatedly perform the task.

Impact:

If you repeat copying repeatedly then it is not the efficient way and you wont achieve the performance.

Solution:

We can use SqlBulkCopy to copy huge data with good performance in Sql Server Database. The Good thing about this class is that it can copy from variety of datasource including xml.

Here is the copy which copies huge number of rows from one table in database to another.

//Create the connection string

string connStr = @"server =yourSqlServername;

                            integrated security = true;

                            database = myDatabaseName";

//Create the connection object and another for bulkcopy

SqlConnection conn = new SqlConnection(connStr);

SqlConnection blkConn = new SqlConnection(connStr);

//Create the command object and populate it with

//desired query

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "Select id from students";

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

//open both connections

conn.Open();

blkConn.Open();

// Create the reader and execute the command

SqlDataReader rd = cmd.ExecuteReader();

//Create the SqlBulkcopy object and supply it with
//bulk copy connection object

SqlBulkCopy bcpy = new SqlBulkCopy(blkConn);

//Supply the destination table to copy data           

bcpy.DestinationTableName = "Results";

//Finally execute the method to copy and pass it with
//data in datareader object

bcpy.WriteToServer(rd);

//Lastly close both the connections

conn.Close();

blkConn.Close();