Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-05-07 : 08:53:38
.NET Framework 2.0 introduces a very handy new class in the System.Data.SqlClient namespace called SqlBulkCopy that makes it very easy and efficient to copy large amounts of data from your .NET applications to a SQL Server database. You can even use this class to write a short .NET application that can serve as a "middleman" to move data between database servers.

Article Link.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-07 : 09:06:23
Excellent article, Jeff !!

Does this technique also works for copying data from different providers, say, from CSV or Excel files?


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-07 : 09:19:48
Anything you can open with a DataReader, you can bulk copy into SQL Server. The destination must always be SQL Server, though.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-07 : 10:06:55
yeah i did almost the whole thesis on importing CSV :)
http://weblogs.sqlteam.com/mladenp/archive/2006/07/22/10742.aspx

here is the sqlbiulkcopy version:
http://weblogs.sqlteam.com/mladenp/archive/2006/08/26/11368.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-07 : 10:13:53
I wonder what would the performance would be like if you used the ODBC textfile driver and just opened an ODBC DataReader on the textfile and then passed the reader to SQLBulkImport, bypassing the internal DataTabel completely. It might be faster, but I'm not really sure.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-07 : 10:38:59
I thought SqlBulkCopy was cool until I started hitting TDS errors with it. This was in a service that needed to be running indefinitely.

the service was inserting about 2000 rows every minute. After about 2 weeks, SqlBulkCopy would start to throw errors related to the TDS stream (can't remember the exact msg now, it's been about a year since then). Once the error was thrown though, every SqlConnection was hosed and couldn't be used for any query (same TDS error would get thrown). This was true even though we were grabbing a new SqlConnection from the pool each time. It was quite befuddling. This was on RTM as I recall, maybe it's fixed in SP1 or SP2 - haven't gone back to trying SqlBulkCopy since the repro time is so long (2 weeks or more).

I still use SqlBulkCopy if it's for a single use type thing, but for a services we use the write file to a share/BULK INSERT method, which there are no problems with except for the admin headache of maintaining the share.

Cool thing about SqlBulkCopy is if you have an array of structs or some such in memory (result of a big number crunchign calculation, say). You just implement a DataReader over your structs and pass it to SqlBulkCopy. that way you don't have to write to a file as an intermediate step. That's why I wanted to use it in my services...


www.elsasoft.org
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-07 : 10:48:52
Great idea about structs and implementing DataReader, I hadn't thought of that.

I wonder if the trouble you encountered was due to SQL Server or the SQLBulkCopy library code or something else ...

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-07 : 12:32:29
About the array of structs thing: there are a ton of methods in IDataReader, but only a few of them are called by SqlBulkCopy, so you can derive from this class to do it, only need to implement 3 methods:


public abstract class SqlBulkCopyReader : IDataReader
{
// derived must implement only these three
public abstract bool Read();
public abstract object GetValue(int i);
public abstract int FieldCount { get; }

// empty methods derived classes may want to implement
public virtual void Close() { }
public virtual void Dispose() { }
public virtual int GetOrdinal(string name) { throw new NotImplementedException(); }
public virtual object this[int i] { get { throw new NotImplementedException(); } }
public virtual int Depth { get { throw new NotImplementedException(); } }
public virtual bool IsClosed { get { throw new NotImplementedException(); } }
public virtual int RecordsAffected { get { throw new NotImplementedException(); } }
public virtual DataTable GetSchemaTable() { throw new NotImplementedException(); }
public virtual bool NextResult() { throw new NotImplementedException(); }
public virtual object this[string name] { get { throw new NotImplementedException(); } }
public virtual bool GetBoolean(int i) { throw new NotImplementedException(); }
public virtual byte GetByte(int i) { throw new NotImplementedException(); }
public virtual long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length) { throw new NotImplementedException(); }
public virtual char GetChar(int i) { throw new NotImplementedException(); }
public virtual long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length) { throw new NotImplementedException(); }
public virtual IDataReader GetData(int i) { throw new NotImplementedException(); }
public virtual string GetDataTypeName(int i) { throw new NotImplementedException(); }
public virtual DateTime GetDateTime(int i) { throw new NotImplementedException(); }
public virtual decimal GetDecimal(int i) { throw new NotImplementedException(); }
public virtual double GetDouble(int i) { throw new NotImplementedException(); }
public virtual Type GetFieldType(int i) { throw new NotImplementedException(); }
public virtual float GetFloat(int i) { throw new NotImplementedException(); }
public virtual Guid GetGuid(int i) { throw new NotImplementedException(); }
public virtual short GetInt16(int i) { throw new NotImplementedException(); }
public virtual int GetInt32(int i) { throw new NotImplementedException(); }
public virtual long GetInt64(int i) { throw new NotImplementedException(); }
public virtual string GetName(int i) { throw new NotImplementedException(); }
public virtual string GetString(int i) { throw new NotImplementedException(); }
public virtual int GetValues(object[] values) { throw new NotImplementedException(); }
public virtual bool IsDBNull(int i) { throw new NotImplementedException(); }
}



www.elsasoft.org
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-05-07 : 17:14:31
Nice Jeff! And a belated "Kudos" to your previous article! (custom sequences)

DavidM

Production is just another testing cycle
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-07 : 18:18:34
quote:
Originally posted by jezemine

About the array of structs thing: there are a ton of methods in IDataReader, but only a few of them are called by SqlBulkCopy, so you can derive from this class to do it, only need to implement 3 methods:



VERY handy info!!! Thank you for posting this.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

DavidBlyth
Starting Member

1 Post

Posted - 2007-05-09 : 05:46:03
In response to a previous comment, I've also encountered a problem with SqlBulkCopy just hanging and eventually timing out (and it was reported to us as a TDS problem). The problem appears to be related to a combination of the number of rows being processed and the batch size. It all worked fine until an unfortunate user tried to write 4031 rows and since then we have found other numbers that will trigger the problem.

See https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=275275


David
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-09 : 09:52:17
quote:
Originally posted by jsmith8858

quote:
Originally posted by jezemine

About the array of structs thing: there are a ton of methods in IDataReader, but only a few of them are called by SqlBulkCopy, so you can derive from this class to do it, only need to implement 3 methods:



VERY handy info!!! Thank you for posting this.

- Jeff
http://weblogs.sqlteam.com/JeffS




you bet. you know how I discovered this right? No way was I going to implement all those methods unless I absolutely had to. So I created a class that threw NotImplementedException from every method, and just kept running the program, implementing one method after another until SqlBulkCopy stopped throwing. Turned out I only had to do those 3.




www.elsasoft.org
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-09 : 11:04:18
Ha, that's funny, that's what I guessed you did .... Well done! That's the beauty of throwing "not implemented" exceptions instead of just leaving the method empty, which I am sure many people do!

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-05-22 : 02:18:50
Hi Peso
the article http://www.sqlteam.com/item.asp?ItemID=26941
is great!
i have 2 qestions :
1)does it work with both sql server 2000/2005 (i assume that yes but just checking)?
2)how or to which object i need to upload rows that i want to read
from a file,and bulk-upload it to to the DB?
(currently i bulk upload from the server that the sql server is running on - but your example is agreat solution for a remote (server) upload!

thnaks in advance
peleg


Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-22 : 12:35:44
Peso?
I think Jeff deserve the credit, since he wrote the article...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-22 : 14:49:08
pelegk2 --

#1) yes

#2) I don't understand your question. Can you try again?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-05-24 : 05:01:11
i want to implemente the upload with text files and not by copying a table from 1 db to another
whats the best way to do it using the given article?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-24 : 12:10:15
if you have text files, you shouldn't use SqlBulkCopy at all. you should use bcp.exe or BULK INSERT.


www.elsasoft.org
Go to Top of Page

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-05-27 : 01:35:45
hi jezemine
but the SqlBulkCopy can solve you problems like for example if you can't access folder's from 1 server to another (there is such a peroblem between win2000 to win2003)
so this can solve this problem beacuse it uses the port to connect to the db.


Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-27 : 11:09:24
there's no such restriction if you use bcp.exe.


www.elsasoft.org
Go to Top of Page

combi
Starting Member

1 Post

Posted - 2007-08-22 : 10:17:43
Hi.

What about calculated values?

When I try to bulkcopy a table with a calculated column, i get a sqlexception.

Any Ideas?
Thany You.
Chris.
Go to Top of Page
    Next Page

- Advertisement -