How to reducing time for bulk copying data from different data table

Question

I have the dataset and it contains different table with different table structure.

Every data table will have more than 70 thousand records and more.

i am trying to move all the data table records using bulk copying method.

I feel that, bulk copying method also will be taking more time to move all the records to different table.

for example,

If i want to move all 70 thousands records in different table, nearly taking 5 to 10 mins.

what is the alternative way to move from data table to Sql table in easy process.

below is my complete code and give us the better solution to handle this time taking issue.

SqlBulkCopy SqlBCopy1 = new SqlBulkCopy(con);
SqlBulkCopy SqlBCopy2 = new SqlBulkCopy(con);
SqlBulkCopy SqlBCopy3 = new SqlBulkCopy(con);
for (int i = 1; i <= 70000; i++) 
{
   SqlBCopy1.ColumnMappings.Clear();
   SqlBCopy2.ColumnMappings.Clear();
   SqlBCopy3.ColumnMappings.Clear();

   connection.Open();
      try
         {
           DataSet.ReadXml(dtReader);

                        SqlBCopy1.DestinationTableName = "tblEmpDetails";
                        foreach (DataColumn col1 in DataSet.Tables[1].Columns)
                            SqlBCopy1.ColumnMappings.Add(col1.ColumnName, col1.ColumnName);
                        SqlBCopy1.WriteToServer(DataSet.Tables[1]);

                        SqlBCopy2.DestinationTableName = "tblEmpSalary";
                        foreach (DataColumn col2 in DataSet.Tables[2].Columns)
                            SqlBCopy2.ColumnMappings.Add(col2.ColumnName, col2.ColumnName);
                        SqlBCopy2.WriteToServer(DataSet.Tables[2]);

                        SqlBCopy3.DestinationTableName = "tblEmpProject";
                        foreach (DataColumn col3 in DataSet.Tables[3].Columns)
                            SqlBCopy3.ColumnMappings.Add(col3.ColumnName, col3.ColumnName);
                        SqlBCopy3.WriteToServer(DataSet.Tables[3]);
}
catch (WebException ex)
 {

 }
                finally
                {
                    connection.Close();
                }

}

Answer 1

For bulk copy to run at max speed, you need to drop the indexes on the target tables. A common practice, is to have a staging table that you bulk load, then use set transactions to move data from staging to live.

Answer 2

Your design goes through a C# project. SQL server has the capability to import XML. Skip the extra C# overhead and import the data into SQL using native features. Read the docs...

Examples of bulk import and export of XML documents (SQL Server)