Bulk Copy From One Data Source To Another Data Source

Bulk Copy :

               Here we will see how to copy data from Excel to Sql tables. First we need to read all the data from excel file then write into sql table.Its step by step process so it takes little more time to complete the process.

For avoiding that we will see some smart way to move the data . ADO.Net provides class sqlbulkcopy . By using this we can read bulk data. And its possible to store it in four ways . Data Set, Data Reader , Xml or DataTable.

Here i used data reader . And following the step will help you all to get a clear idea.

1. Open the Source connection and Read data from the source.

eg: oledbconnection=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source= ” + sourcefile + “;Extended Properties=Excel 8.0;“;OleDbConnection

sourceConnection = new
OleDbConnection(oledbconnection);

2. Store it in a temp file using sqlbulkcopy object .(For dynamic excel sheet read)

OleDbCommand 

command = new OleDbCommand(Constants.SqlQuery.ReadExcelData + ” [" + excelSheets[0] + “]”  , sourceConnection);

OleDbDataReader

reader=command.ExcuteReader();

3.Open the destination connection.
destinationConnection.Open();
 
SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection); bulkCopy.ColumnMappings.Add(“columnName”
 
,”ColumnName”);

4.Move data from sqlbulk copy object to destination .
bulkCopy.DestinationTableName =”DestinationTableName”

;
Now you can see all the data has been copied from source to destination.

command=new SqlCommand(“UserQuery to fetch data from temp file to insert into table”, destinationconnection)