SQLBulkCopy

Question

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,

Senthilkumar · Answer

Hi MSDN: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx E fficient bulk operations: http://www.4guysfromrolla.com/articles/102109-1.aspx Q uick load from client into sql server: http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server

samunder singh · Answer

I used a very good post on this at blogershub.com for How to upload bulk data in database using SqlBulkCopy . 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 Horvat · Answer

Darnell I've written an article on this topic - solution that I've constructed is capable to insert millions of records per minute in parent-child related tables in SQL Server database. Here is the article: How to Bulk Insert Complex Objects into SQL Server Database Zoran

Veera Chenna · Answer

Its the same if you are loading from excel to DB or flat file to DB. THe only thing that would change is the connection string & sql for getting the source data //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 link http://technico.qnownow.com/2012/04/02/bulk-copy-data-from-flat-file-text-csv-to-sql-server-using-sql-bulk-copy/ Regards Veera

David Smith · Answer

I am not loading data from excel I am loading data from a text delimited file

Veera Chenna · Answer

I would recommend SQLBulkCopy. It is pretty fast and you would have more control especially if you want to do some data manipulations per se. You could go to following links for complete example 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 Smith · Answer

Can you give me an example of SSIS or point to an give web site regarding SSIS. What is SSIS?

Suthish Nair · Answer

I would suggest SSIS...