Using partitions to load table data

You can use partitioning to expedite the loading of large amounts of table data, even when the table will eventually be used as an unpartitioned table.

Use the round-robin-partitioning method, and place all partitions on the same segment.

1. Create an empty table, and partition it n ways:

   create table currentpublishers
   (pub_id char(4) not null,
   pub_name varchar(40) null,
   city varchar(20) null,
   state char(2) null)
   partition by roundrobin 3 on (seg1)
   

2. Run bcp in using the partition_id option. Copy presorted data into each partition. For example, to copy datafile1.dat into the first partition of currentpublishers, enter:

   bcp pubs2..currentpublishers:1 in datafile1.dat
   

3. Unpartition the table:

   alter table currentpublishers unpartition
   

4. Create a clustered index:

   create clustered index pubnameind
   		on currentpublishers(pub_name)
   

   		with sorted_data
   

When the partitions are created, Adaptive Server places an entry for each one in the syspartitions table. bcp in with the partition_id option loads data into each partition in the order listed in syspartitions. You unpartitioned the table before creating the clustered index to maintain this order.