I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Bulk import text files using .net 2.0 SqlBulkCopy class in C#

I've written about bulk insert methods for text files in sql server 2005.
.Net 2.0 brings the SqlBulkCopy Class in System.Data.SqlClient namespace.
Speed wise it can't even compare to native sql server methods but it's usefull
if it's the only viable solution.

SqlBulkCopy.WriteToServer has 4 overloads:
SqlBulkCopy.WriteToServer (DataRow[])
    Copies all rows from the supplied DataRow array to a destination table specified by the
    DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (DataTable)
    Copies all rows in the supplied DataTable to a destination table specified by the
    DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (IDataReader)
    Copies all rows in the supplied IDataReader to a destination table specified by the
    DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (DataTable, DataRowState)
    Copies only rows that match the supplied row state in the supplied DataTable to a
    destination table specified by the DestinationTableName property of the SqlBulkCopy object.

When importing text files with this method you have to create a DataTable first, import the text file
to the created DataTable and then write this DataTable to server.

With this we're acctually performing 2 tasks in .net:
1. Fill data from text file to DataTable in memory
2. Fill data from DataTable in memory to SQL server

Compared to SQL servers native bulk import methods where we just import the text file directly.

I used the same file and the same table structure as in previous bulk import methods described here.
The time it took to complete the whole process was around 30 seconds.

This is the code i used for import:
private void StartImport()
{
    Stopwatch sw = new Stopwatch();
    sw.Start();
    SqlBulkCopy bulkCopy = new SqlBulkCopy("Server=ServerName;Database=test;Trusted_Connection=True;", 
SqlBulkCopyOptions.TableLock); bulkCopy.DestinationTableName = "dbo.testSQLBulkCopy"; bulkCopy.WriteToServer(CreateDataTableFromFile()); sw.Stop(); txtResult.Text = (sw.ElapsedMilliseconds/1000.00).ToString(); } private DataTable CreateDataTableFromFile() { DataTable dt = new DataTable(); DataColumn dc; DataRow dr; dc = new DataColumn(); dc.DataType = System.Type.GetType("System.Int32"); dc.ColumnName = "c1"; dc.Unique = false; dt.Columns.Add(dc); dc = new DataColumn(); dc.DataType = System.Type.GetType("System.Int32"); dc.ColumnName = "c2"; dc.Unique = false; dt.Columns.Add(dc); dc = new DataColumn(); dc.DataType = System.Type.GetType("System.Int32"); dc.ColumnName = "c3"; dc.Unique = false; dt.Columns.Add(dc); dc = new DataColumn(); dc.DataType = System.Type.GetType("System.Int32"); dc.ColumnName = "c4"; dc.Unique = false; dt.Columns.Add(dc); StreamReader sr = new StreamReader(@"d:\work\test.txt"); string input; while ((input = sr.ReadLine()) != null) { string[] s = input.Split(new char[] { '|' }); dr = dt.NewRow(); dr["c1"] = s[0]; dr["c2"] = s[1]; dr["c3"] = s[2]; dr["c4"] = s[3]; dt.Rows.Add(dr); } sr.Close(); return dt; }

Print | posted on Saturday, August 26, 2006 9:03 PM | Filed Under [ .Net SQL Server ]

Feedback

Gravatar

# Interesting Finds: August 26, 2006

8/26/2006 7:16 PM | Jason Haley
Gravatar

# .Net 2.0 のDataTable から、直接 BULK INSERT するサンプルコード(MladenさんのBlog)

.Net 2.0 ?DataTable ????? BULK INSERT ?????????(Mladen???Blog)
8/31/2006 9:41 AM | 米田 Blog ( SQL Server MEMO )
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

that was my point also.
importing text files with SqlBulkCopy isn't worth it.
Unless you can show me a fast way to make a IDataReader out of text file data.
9/8/2006 5:56 PM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

we agree then :))
using SqlBulkCopy on the text file on the disk is needless since sql server provides better tools.

could you possibly send me the code or the program you used so i could do a speed comparison on my computer?
since i've already posted a few posts about this. look at the july section of my blog.

have you tried tablock with bulk insert?

IMO SqlBulkCopy is a very usefull class and can come in very handy when trying to do bulk inserts of large structs in memory.

thanx.
9/12/2006 10:48 AM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

try bulk insert with tablock. speed gain is quite noticable.

and yes. SqlBulkCopy is a good class :))
9/13/2006 11:52 AM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

How do you insert values into a table with an identity field? My attempts are failing, nothing gets inserted.

Example:
Table "Customers" fileds: "CustomerId" (identity) and "Name" (Varchar)

My text file has names, but obviously not an ID. I create a DataTable with the table name, and just the "Name" field populated (leaving NO column in my datatable for identity, because I dont actually want to insert anything there)

Now when I bulk copy from DataTable (full of names) to Sql table using WriteToServer(), nothing happens, no inserts, no errors/exceptions. I was expecting the data to be loaded, and the identity field to simply populate automatically as you would expect.

If I bullk copy this data into a table with no identity field, it works fine, so I know all my other code is fine.

Can anyone offer insight here? How the heck do you handle identity fields when using SqlBulkCopy ???
9/14/2006 10:45 PM | neoscoob
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

BULK INSERT may be very fast, I found it a pain to use in real life because of the shared drive between clients and the SQL server.

We had so much admin troubles with the shared drives of our clients... it was insane, but it was the only solution before this SQLBulkCopy class (thank god it came !).
10/5/2006 2:03 PM | Sylvain Ross
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Thank god for this thing. I was going insane trying to process about 93 million records that the flat text reader wouldn't process. I'm already up to 23 million done in 20 minutes. The solution for eating up resources is to dump the datatable every million (or whatever) rows or so, clear the rows and collect the garbage. Keeps the memory lower that way.
10/5/2006 9:23 PM | m_drunk
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

"try bulk insert with tablock. speed gain is quite noticable."

SqlBulkCopy also supports TABLOCK (which is off by default).
10/10/2006 7:55 PM | scott
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Yes it's SqlBulkCopyOptions.TableLock
As shown in the example code.
10/10/2006 8:02 PM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Just wondering how well would SqlBulkCopy manage transactions ? Especially when inserting to multiple tables at once where first table insert has to return an ID-parameter for the rest of the transaction to success. I can do this with regular ADO.NET, but what about SqlBulkCopy ?
2/28/2007 11:56 AM | Timo
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

it handels them quite well.

more info:
http://msdn2.microsoft.com/en-us/tchktcdk.aspx
2/28/2007 12:09 PM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Great Article! I changed my application from using the DTS to this.

However, I'm running into problem with dc.DataType = System.Type.GetType("System.String") for SQL data type of nVarChar.

I got an error saying "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column."

Any help is appreciated!

Thanks,
TA
4/2/2007 7:30 PM | Abbey
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

try using System.Data.SqlTypes.SqlString
4/2/2007 7:32 PM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Try benchmarking against my IDataReader implementation, http://www.csvreader.com . There's no reason to create a DataTable and load data into memory when it doesn't need to be there.
4/24/2007 3:43 PM | Bruce Dunwiddie
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

thanx for the suggestion.

i'll try it when i have time.
4/24/2007 3:55 PM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

why is the files bulk low?
assuming i dont want to install sql server client to bulk from it, the last solution is to use the NET bulk upload.
or any other fast way to bulk uppload text files from a rem,pte serveR?
5/22/2007 8:23 AM | peleg
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

i don't really understand your question, peleg.
can you put it differently?
5/22/2007 11:15 AM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Array Binding
http://download-west.oracle.com/docs/html/A96160_01/features.htm#1049674
8/15/2007 4:06 PM | Pazout
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

I have a table previously loaded with data. Is there any way I can perform a bulk copy operation which only loads additional data instead of re-importing rows?
9/11/2007 5:26 PM | JimNet
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

not that i know of.
9/12/2007 9:52 AM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Mladen, thanks for the example,
I used it as a basis for adding BULK INSERT support to my LINQ provider for MS SqlServer.

http://code2code.net/DB_Linq/LINQ_and_SQL_Bulk_Insert.html
10/25/2007 10:09 AM | george moudry
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

excellent!
10/25/2007 10:09 AM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

i have semiler problem,

i have a handpunch machine for 400 employees it give us data in a text file, i have HeRMS system on SQL server . I need to make an interface to copy data from this text file to sql table (data_card).

please advice me

thanks
bahawodin
11/28/2007 5:46 AM | bahawodin
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Thanks for Great article. It helps me a lot.
11/29/2007 10:19 PM | Haribabu
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#


this does not work...

dc.DataType = System.Type.GetType("System.Data.SqlTypes.SqlString");


exception is "Column requires a valid DataType."

Any ideas?

I need to set the type as a nvarchar
12/4/2007 1:47 PM | alan
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Alan, it could be that one of the columns in your table is too small for the string (doesn't have to be SqlString, I don't think) you are trying to insert; e.g., you have nvarchar(30) and the string is actually 40 chars long. See this thread for a bit more detail:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3186813&SiteID=1
4/16/2008 3:44 PM | Ed Graham
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

What if you have a very large text file, let's say a 2 GB file with 15 million records... or double that size. My guess is that it would it fail to load it in the memory. BULK INSERT works like a charm, I will try SqlBulkCopy just to see what happens.
6/20/2008 5:40 AM | Adrian
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Re: "What if you have a very large text file, let's say a 2 GB file with 15 million records... or double that size. My guess is that it would it fail to load it in the memory. BULK INSERT works like a charm, I will try SqlBulkCopy just to see what happens."

Actually, I ran into a problem with BULK INSERT when I tried to upload the data into a remote SQL server... the actual text file specified in the BULK INSERT command has to be local or somehow accessible from the remote SQL server. I've managed to implement SqlBulkCopy and avoid filling up the memory by flushing the temporary working tables every 100k records. It works pretty fast too!
6/20/2008 9:43 PM | Adrian
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

What happens to the bulk insert (SqlBulkCopy) if the inserts potentially inserts duplicate rows and violate the primary key? Will it ignore the dups and continue or die?

thanks!
6/23/2008 5:44 PM | perry
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

it will die.
you can however set the IGNORE_DUP_KEY on the index to let it insert only non duplicated rows.
6/23/2008 6:09 PM | Mladen
Gravatar

# SQL BULK COPY with Identity Column

If a table has an Identity column, create a view of the table with each column except the Identity column. Do a SQL BULK COPY or BULK INSERT into the view.

9/4/2008 10:57 AM | Kevin
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Hi There,

Re: I've managed to implement SqlBulkCopy and avoid filling up the memory by flushing the temporary working tables every 100k records. It works pretty fast too!

Can you show me the code how to flash the temporary working files. I have many CSV files about 20 to 24 mil records each, and it consumes all memory for big files. I am also using SQLBulkCopy and Closing the Visual Studio will not release the memory anyway.

Any idea ?

Thanks,

11/9/2008 2:20 AM | Steve
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

I just stumbled across this thread and thought it may be beneficial to share some of my discoveries using SQLBulkCopy.

After much experimentation, the quickest way to load tables I've found is to utilize SQLBulkCopy using multiple threads.
To do this I perform the following steps:

1) Create a two member array of dataset
2) Load the first dataset from a flat file. (Performance is helped significantly by using getordinal and saving the index of the
column instead of repeatedly using the quoted column name)
3) Call the writetoserver method of SQLBulkCopy to commit the data in a new thread.
At the end of this commit process I clear the dataset
4) While step 3 is running, I fill the other dataset.
5) Once it is full I let the threads synchronize
6) Repeat step 3 committing the second dataset, step 4 loads the first dataset, ...

This works well if you have the flat file data on a separate machine from the SQL Server, and run the SQLBulkCopy application on the non-SQL machine. The load of the in-memory dataset consumes cycles from your non-SQL machine leaving the SQL-Server free to only load the data. We typically load around 300 million rows once per week using this process and have not found a faster way yet.

I am considering looking into what would be involved in creating a backup image from the raw data and then doing a restore. I only say this because the full loads take around 12 hours and a backup and restore of the same database takes less than half of that time. (My guess is that logging overhead is responsible for most of the time).
11/21/2008 6:17 PM | Craig
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Hi Craig,

Re: just stumbled across this thread and thought it may be beneficial to share some of my discoveries using SQLBulkCopy.

Can you please share some of your code. Since when I am running several files between 1 to gb of CSV files it consumes almost all memory available. In this case I need to re-Start the sql service to clear it up.

Thanks,

Steve
11/28/2008 4:29 PM | Steve
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

For all those who ask all the time: "BULK INSERT is better, why are you using sqlBulkCopy???"

Yes, BULK INSERT is better in performance, you don't need to load data into memory, bla, bla, bla... BUT what happens if you need to treat your data in memory BEFORE the inserts?

Maybe I am wrong but I think:
A- For loading a plain file directly into DB --> Use Bulk INSERT
B- For loading a plain file treating and modofying your data before loading it into the DB --> Use sqlBulkCopy.
C- For something like B but with huge files (over 2GB data files), use whatever fits better your environment:
C1- Treat your data and save it directly in a temp file, then use BULK INSERT.
C2- Treat your data in memory then use sqlBulkCopy from time to time in order yo free your DataTable memory, also using multithreading.
12/17/2008 11:42 AM | Kyopaxa
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Thnx Ed Graham,

4/16/2008 3:44 PM | Ed Graham

That was my problem

2/9/2009 11:00 AM | Eslam Amgad
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C# with an Identity field

To import any data into SQL using SQLBulkCopy where the table has an identity field simply create a view of the table, excluding the identity field, then SQLBulkcopy into the view.
4/23/2009 8:27 AM | Kaytch
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

RE: "I just stumbled across this thread and thought it may be beneficial to share some of my discoveries using SQLBulkCopy.

After much experimentation, the quickest way to load tables I've found is to utilize SQLBulkCopy using multiple threads."

Would you be able to share some of the code on how to implement multiple threads?

I'm also trying to load multiple flat files into sql and would like to know how to do it with multiple threads.

Thanks!
8/6/2009 9:58 PM | CT
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Problem from previous above topics:
To import any data into SQL using SQLBulkCopy where the table has an identity field simply create a view of the table, excluding the identity field, then SQLBulkcopy into the view.

Solution:
No need to create views and put data from datatable into views. You just have to map the columns you want unless miss match column name.


using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
{
bcp.DestinationTableName = "issuer_backup";
bcp.BatchSize = 2000;
bcp.BulkCopyTimeout = 300; // 300 seconds

// map columes of DataTable with Fields in database Table, if the same name , no need
bcp.ColumnMappings.Add("ID", "ID");
bcp.ColumnMappings.Add("Folder_name", "Folder_name");
bcp.ColumnMappings.Add("Stock", "stock");

//
bcp.WriteToServer(Datatable1);

}


Hope it useful!!!!

9/23/2009 9:28 AM | SV-Thailand
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

I want to insert 10000000 rows ...Is this possible through above code ... i will try but my PC is in hanged position....
Pls Help
12/22/2009 1:24 PM | Mohit
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

yes it is possible but i don't think you'll be able to put it all in memory.
12/22/2009 1:48 PM | Mladen
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Mladen, thanks for the example,
I used it a a basis for adding BULK INSERT support to my LINQ provider for MS SqlServer.

http://code2code.net/DB_Linq/LINQ_and_SQL_Bulk_Insert.html
1/21/2010 10:58 AM | Target
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

I just ran the Copy Database Wizard myself on my SQL Server 2005 and I suspect the problem you are running into is due to the account that SQL Agent runs under. You need to create a SQL Agent Proxy account that can login to the remote machine and then use this account. By default the SQL Agent service account does not have remote access.
2/23/2010 11:59 AM | Jackpots del casino
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Please find one of the easiest working source here

string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + uploadFileDialog.FileName.Substring(0, uploadFileDialog.FileName.LastIndexOf('\\')) + "; Extensions=asc,csv,tab,txt; Persist Security Info=False";
string sql_select;
OdbcConnection conn;
conn = new OdbcConnection(strConnString.Trim());
conn.Open();
//Counts the row number in csv file - with an SQL query
OdbcCommand commandRowCount = new OdbcCommand
("SELECT COUNT(*) FROM [" + uploadFileDialog.FileName.Substring(uploadFileDialog.FileName.LastIndexOf('\\'), uploadFileDialog.FileName.Length - uploadFileDialog.FileName.LastIndexOf('\\')).Remove(0, 1) + "]", conn);
int rowCount = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
DataSet ds = new DataSet();
// Creates the ODBC command
sql_select = "select * from [" + uploadFileDialog.FileName.Substring(uploadFileDialog.FileName.LastIndexOf('\\'), uploadFileDialog.FileName.Length - uploadFileDialog.FileName.LastIndexOf('\\')).Remove(0, 1) + "]";
OdbcCommand commandSourceData =
new OdbcCommand(sql_select, conn);
OdbcDataAdapter da = new OdbcDataAdapter(commandSourceData);
da.Fill(ds);
System.Data.SqlClient.SqlBulkCopy sqlCopy = new SqlBulkCopy("Data Source=localhost;Initial Catalog=TechAnalysis;User ID=sa;Password=Mar@1980;Connection Timeout=120;Max Pool Size=1500;", SqlBulkCopyOptions.TableLock);
sqlCopy.DestinationTableName = "dbo.AnalysisData";
sqlCopy.BatchSize = ds.Tables[0].Rows.Count;
sqlCopy.WriteToServer(ds.Tables[0]);
3/8/2010 12:44 PM | Chid
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

How can I find already inserted rows through SQ:BulkCopy program?
3/24/2010 2:25 AM | Ashish Dhar
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

Hi,

I am tyring to bulkinsert from the data i obtain from AD into memory (C# .net) and then bulk insert that into sql DB. The problem is the sql db contains two tables with parent child relationship. Whereever i read about sqlbulkcopy it seems to be only on one table:

1) Is it possible to do sqlbulkcopy on multiple tables at the same time
2) Is it possible to do sqlbulkcopy on tables which have foriegn key constraint.

Any insight would be helpful

Thanks.
5/28/2010 8:32 PM | Beulah Nelson
Gravatar

# re: Bulk import text files using .net 2.0 SqlBulkCopy class in C#

hi...
i'm not familiar with programming and currently my project need to use it. I have the csv log files and want to import those data to the sql server table at other PC. I don't want to import all of the columns of csv and i just want selected columns to server. Eg, column 1 and 3 of a.csv file to column 1 nd 2 of server table and column 1 and 3 of b.csv file to column 3 and 4 of server table and so on. Could you subject me how to start?

thanks,
Thinn
7/2/2010 4:17 AM | Thinn
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET