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.
| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2004-04-13 : 19:29:39
|
| Is there anything that's faster than BCP to export data? Current, it's taking me about 2 minutes or so to get about 70,000 rows out into a tab-delimited file. I'm then using BULK INSERT to get it into my other database (for data warehouse stuff). I'd like something as fast as BULK INSERT, except for data exporting. Any ideas? Everything I've read said BCP is the fastest thing going.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-04-13 : 19:35:15
|
| Michael,Have your tried the native switches in BCP -n or -N.....Check out the BCP GUI for testing the different switches......[url]http://weblogs.sqlteam.com/davidm/archive/2004/03/31/1151.aspx[/url]DavidM"If you are not my family or friend, then I will discriminate against you" |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-04-13 : 19:39:56
|
| When I was using Native Mode, I could not get it to import for some reason. Maybe I should go back and try to get that working. Right now, I'm just using a -c.Thanks!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-04-13 : 19:46:01
|
| native will require exactly the same schema.. datatypes, column position.. everything...Also look at the network packet size the -a switchDavidM"If you are not my family or friend, then I will discriminate against you" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-13 : 19:48:05
|
| Also, play around with -b and -a switches.-a:/a packet_size The number of bytes contained in a network packet. The default value for Windows NT Server and Windows NT clients is 4096 ; the default value for MS-DOS clients is 512. Valid sizes are 512 to 65535 bytes (optional). Tip: Depending own your network architecture, you may be able to improve BCP performance by increasing the packet size. Try setting the packet size between 4096 and 8192 bytes. Use the statistics returned by BCP (the clock time and rows per second) to help tailor this setting.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-13 : 19:49:01
|
| Dang, David beat me to it. I was copying the information in for -a switch when a co-worker distracted me. Oh well.Tara |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2004-04-13 : 21:06:01
|
| Check out Mr. Drapper's presentations on fast data loading.http://www.sqldev.net/events.htm |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-14 : 11:53:08
|
| Hey how about this one,,assuming it's a different server?SELECT * INTO linked_server.dbname.owner.Stage_Table FROM myTable99Brett8-) |
|
|
|
|
|
|
|
|
Faster than BCP?