Can someone lead me to a good tutorial using SQLBulkCopy.
Basically i am importing data from a test file. Tell what would be the best approach. I am importing over a million into the database. Is SQLBulkCopy going to give the best performance. IF so can someone provide a tutorial how to successfully use SqlBulkCopy.
I am going to give you the structure I am using. I was planning on inserting into a temporary table first, then after I get the temporary table loaded I wanted to use the SQlBulkCopy to write the date to the SQL Server 2008. to the table below. can someone assist me.
I am going to create table in code with the same structure
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("ColorSerial", typeof(string)));
dt.Columns.Add(new DataColumn("ColorPart", typeof(string)));
dt.Columns.Add(new DataColumn("ColorDescription", typeof(string)));
DataRow nextRow;
nextRow = dt.NewRow();
nextRow["ColorSerial"] = colorSerial;
nextRow["ColorPart"] = colorPart;
nextRow["ColorDescription"] = colorDescription;
Table Name: Color
Columns: Color Serial, Color Part, Color Description,
Loading
SenthilkumarPosted Apr 2, 2012, 11:32 PM
MSDN:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
Efficient bulk operations:
http://www.4guysfromrolla.com/articles/102109-1.aspx
Quick load from client into sql server:
http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server
samunder singhPosted Nov 29, 2013, 1:44 AM
Above post is having all accepts to take care while uploading data in database.
private void BulkUpload(DataTable dt) { dt.TableName="YourDataTable"; string constr="your connection string"; using(SqlConnection connection=new SqlConnection(constr)) { connection.Open(); //CreatingTranscationsothatitcanrollbackifgotanyerrorwhileuploading SqlTransaction trans=connection.BeginTransaction(); //Start bulkCopy using(SqlBulkCopy bulkCopy=new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock| SqlBulkCopyOptions.FireTriggers, trans)) { //Setting timeout to 0 means no time out for this command will not timeout until upload complete. //Change as per you bulkCopy.BulkCopyTimeout=0; bulkCopy.DestinationTableName=dt.TableName; //write the data in the "dataTable" bulkCopy.WriteToServer(dt); } }}Zoran HorvatPosted Apr 14, 2013, 7:35 AM
Zoran
Veera ChennaPosted Apr 2, 2012, 6:06 PM
//connection string changes depending on the operation system you are running
string sourceConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Temp\;
Extended Properties=text;";
DataTable sourceData = new DataTable();
using (OleDbConnection conn =new OleDbConnection(sourceConnString))
{
conn.Open();
// Get the data from the source table as a SqlDataReader.
OleDbCommand command = new OleDbCommand(@"SELECT * from Products.txt", conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(sourceData);
conn.Close();
}
You would find the complete example at the below linkhttp://technico.qnownow.com/2012/04/02/bulk-copy-data-from-flat-file-text-csv-to-sql-server-using-sql-bulk-copy/
Regards
Veera
David SmithPosted Mar 27, 2012, 9:45 PM
Veera ChennaPosted Mar 27, 2012, 5:40 PM
Loading data from excel file to SQL Server DB
http://technico.qnownow.com/2012/03/27/bulk-copy-data-from-excel-to-destination-db-using-sql-bulk-copy/
Copying data from one SQL Server DB to another SQL Server DB
http://technico.qnownow.com/2012/03/27/using-sql-bulk-copy-to-efficiently-load-data-from-source-db-to-destination-db/
David SmithPosted Mar 23, 2012, 12:54 PM
Suthish NairPosted Mar 23, 2012, 10:20 AM