bcp in表数据时候如何分割大的数据表？

laoma1994

分批bcp out，分批bcp in

c001100c

加什么参数据能分批 BCP OUT ；BCP IN

leno_mx

前面的帖子不是说了吗，加-F -L参数。

YtBenjamin

>Create view viewname as select * from You-want-to-bcp-tablename where time>'2004/1/1'  
   and time<'2004/5/1'
>go

cmd>bcp DBname..viewname out viewname.bcp -Uusername -Ppassword -Sdbname -c
cmd>bcp DBname..viewname in viewname.bcp -F1 -L500000 -Uusername -Ppassword -Sdbname -c
>dump tran dbname with trun_only
>go
.............................
..............................................

zhitaoz

我刚导完两个数据库，都是把大表的主建和INDEX还有关联的触发器删除，等数据BCP进去好再从新建，个人感觉还是比较快的！

2BeSybPro

This is the way to do it:
1. sp_dboption db_name,'select into/bulkcopy',true
2. sp_dboption db_name,'trunc log on chkpt',true
3. drop indexes,triggers on destination tables
   BCP won't fire triggers, but bcp program is optimized for tables without triggers.
4. bcp out data using either of the following methods,
    4.1 bcp out the whole data
    4.2 bcp ... out data1.txt ... -F1 -L10000000 ..... (use the last row number you want)
          bcp ....out data2.txx ... -F10000001 -L20000000..
5. bcp in data
    5.1 bcp in the whole data using -b option, say 5000, if you bcp version is higer than 12.0,otherwise
       5.1.1 use split -l linecount to split your file
       5.1.2 use FIFO pipe
   5.2 bcp ... in ...data1.txt ... -b 5000
         bcp ... in ...data2.txt ... -b 5000 ......
6. rebuild indexes, recreate triggers

I used bcp to move data from 12.5.2 to 12.0 (bcp 12.0 for solaris doesn't support files greater than 2G), there are 86 million records in one table, and 32 million records in another one, it took me 10 hours in total.

jtyhyl

原帖由 flybean 于 2005-11-28 16:28 发表
还是要使用快速BCP，导完数据后，再建主键和索引，比有主键和索引时导入快多了，并且还可以使用并行。

正解，先删除主键及索引，导完后再建不会有日志且速度很快