Tuesday, May 10, 2011

SQL Bulk Copy

SQL Bulk Copy is for moving data from one Table to another. This can be from a single server or between different servers (Source and destination are different servers).
SqlBulk copy class can be used to write data only to sql server tables. Any data source can be used for loading data as long as the data can be loaded to a Data Table or read with an IDataReader instance.
In The example mentioned below, I am using Oracle Table as source and Sql Server Table as destination. Also DataReader is using to fetch data from oracle.

SQL Bulk Copy Example

public void BulkCopyMethod()
{
string oracleConnectionString = "Data Source=SERVICENAME;User Id=USERNAME;Password=PASSWORD;";
string sqlConnectionString = "Data Source=DATASOURCE;Initial Catalog=DATABASENAME;Connect Timeout=0;User ID=USERNAME;Pwd=PASSWORD;";
string oracleSelectStatement = "SELECT EmployeeID,EmployeeName,EmployeeAge,EmployeePlace,EmployeePin,EmployeePhoneNum FROM EmployeeData";

using (OracleConnection oracleConn = new OracleConnection(oracleConnectionString))
{
oracleConn.Open();
OracleCommand employeeCommand = new OracleCommand(oracleSelectStatement, oracleConn);
OracleDataReader employeeReader = employeeCommand.ExecuteReader();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "dbo.tblEmployee";
bulkCopy.ColumnMappings.Add("EmployeeID", "Id");
bulkCopy.ColumnMappings.Add("EmployeeName", "Emp_Name");
bulkCopy.ColumnMappings.Add("EmployeeAge", "Emp_Age");
bulkCopy.ColumnMappings.Add("EmployeePlace", "Emp_Place");
bulkCopy.ColumnMappings.Add("EmployeePin", "Emp_Pin");
bulkCopy.ColumnMappings.Add("EmployeePhoneNum", "Emp_Phone");

try
{
bulkCopy.WriteToServer(employeeReader);
}
catch (Exception ex)
{
throw ex;
}
finally
{
bulkCopy.Close();
employeeReader.Close();
employeeCommand.Dispose();
oracleConn.Close();
}
}
}
}

For more info:- http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

1 comment: