BCP to bulk copy from view to data file

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BCP to bulk copy from view to data file

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-15 : 07:28:43

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

3 Posts

Posted - 2005-08-19 : 02:45:07

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

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-08-19 : 02:53:24

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 02:57:34

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

jaydeepv
Starting Member

3 Posts

Posted - 2005-08-19 : 03:05:13

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

lechu777
Starting Member

1 Post

Posted - 2009-02-24 : 15:09:24

I have a same problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources