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
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 BCP to bulk copy from view to data file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/15/2005 :  07:28:43  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Jaydeep writes "Hi...
I am using BCP utility to bulk copy from database view to data file. To be more specific please see below my bcp command

bcp "select * from MyDB.dbo.MyView_vw order by my_number" queryout ide.txt /c /S<serverIP> /Usa /Psa /t /omyOutput.txt /emyError.txt
(Currently I have 751318 rows in my DB to be copied)
Now by default BCP command copies/writes 1000 rows at a time. My question is can I increase this batch size to 100000 ?
I tried using /b option but did not work for me.

Is there any work around for the same? Can I change the default bath size 1000?

Thanks and Regards
Jaydeep"

jaydeepv
Starting Member

India
3 Posts

Posted - 08/19/2005 :  02:45:07  Show Profile  Reply with Quote
Please let me know if the question is not clear. I am the same person who posted the question (chaning bcp batch size while exporting data to text). I want to change batch size from default 1000 to 100000

Thanks and Regards
Jaydeep
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 08/19/2005 :  02:53:24  Show Profile  Reply with Quote
It's my understanding that the batch switch is limited to importing data into sql server.

Is there any other problem? Too slow?

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 08/19/2005 :  02:57:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I am not sure about this
you can use batch parameter

In SQL Server Books On Line (help file)
Goto index and type bcp utility
and select batch parameters
and read that

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jaydeepv
Starting Member

India
3 Posts

Posted - 08/19/2005 :  03:05:13  Show Profile  Reply with Quote
There is no problem while exporting data, it is just that I want to change default batch size while exporting. Why I want to do it because, I have about more that 3 Millions of records to be exported. Now when I used bcp command, please see my output file to give you an example...

If you observed it writes 1000 records at a time, I want to change this to 100000. Can I do it, that is my question. Still have any doubts let me know.


Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
1000 rows successfully bulk-copied to host-file. Total received: 6000
1000 rows successfully bulk-copied to host-file. Total received: 7000
1000 rows successfully bulk-copied to host-file. Total received: 8000
1000 rows successfully bulk-copied to host-file. Total received: 9000
1000 rows successfully bulk-copied to host-file. Total received: 10000
1000 rows successfully bulk-copied to host-file. Total received: 11000
and so on....
1000 rows successfully bulk-copied to host-file. Total received: 751000

751318 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 176861
Go to Top of Page

lechu777
Starting Member

Argentina
1 Posts

Posted - 02/24/2009 :  15:09:24  Show Profile  Reply with Quote
I have a same problem
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.06 seconds. Powered By: Snitz Forums 2000