SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Use SqlBulkCopy to Quickly Load Data from your Client to SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/07/2007 :  08:53:38  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
.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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 05/07/2007 :  09:06:23  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

USA
7423 Posts

Posted - 05/07/2007 :  09:19:48  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Slovenia
11750 Posts

Posted - 05/07/2007 :  10:06:55  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

USA
7423 Posts

Posted - 05/07/2007 :  10:13:53  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/07/2007 :  10:38:59  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

USA
7423 Posts

Posted - 05/07/2007 :  10:48:52  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/07/2007 :  12:32:29  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Australia
1591 Posts

Posted - 05/07/2007 :  17:14:31  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 05/07/2007 :  18:18:34  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

United Kingdom
1 Posts

Posted - 05/09/2007 :  05:46:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/09/2007 :  09:52:17  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

USA
7423 Posts

Posted - 05/09/2007 :  11:04:18  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Israel
723 Posts

Posted - 05/22/2007 :  02:18:50  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
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

Sweden
30213 Posts

Posted - 05/22/2007 :  12:35:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
7423 Posts

Posted - 05/22/2007 :  14:49:08  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Israel
723 Posts

Posted - 05/24/2007 :  05:01:11  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/24/2007 :  12:10:15  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Israel
723 Posts

Posted - 05/27/2007 :  01:35:45  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/27/2007 :  11:09:24  Show Profile  Visit jezemine's Homepage  Reply with Quote
there's no such restriction if you use bcp.exe.


www.elsasoft.org

Edited by - jezemine on 05/27/2007 11:11:21
Go to Top of Page

combi
Starting Member

1 Posts

Posted - 08/22/2007 :  10:17:43  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000