BCP out/in very large table

Could someone give me some guidance on the fastest way to unload and reload
data from very large tables. I am planning to drop about 30% of columns
(fixed length columns) from few of our largest tables (size ranges from 300
GB to 500 GB each). In order to reclaim space from dropped columns, looks
like only way is to unload the data, recreate the table (with less columns)
and reload the data. Dropping and recreating clustered index didn't reclaim
space ( I am running SQL 2000 sp4).
I am planning to perform following steps:
1. BCP out data
2. Drop/recreate table with only clustered index
3. BCP in data
4. Create non clustered index, foreign key, constraints etc
Do you see any potential problem with exporting such a big size to text
file? I am planning to export the data to multiple files so that when I BCP
in, I can load them concurrently and also planning to use ORDER and TABLOCK
hint. Can I use ORDER and TABLOCK hint when loading data to a table with
clustered index and loading multiple file simultaneously? I am guessing if I
use "queryout" option with "order by" clause on clustered column when bcp
out the data, I should be able to use ORDER hint when loading data in. Let
me know if I am wrong. Also, I am planning to use SQL server Native format.
If you have any experience in unloading/reloading very large table from sql
server, I would love to hear your comments/suggestion.
Instead of BCP in, consider BULK INSERT with parallel streams. See the
following excellent articles for some of the best rpactices recommendations:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx
http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx
http://www.microsoft.com/sql/techinfo/administration/2000/rosetta.doc
Linchi
"james" wrote:

> Could someone give me some guidance on the fastest way to unload and reload
> data from very large tables. I am planning to drop about 30% of columns
> (fixed length columns) from few of our largest tables (size ranges from 300
> GB to 500 GB each). In order to reclaim space from dropped columns, looks
> like only way is to unload the data, recreate the table (with less columns)
> and reload the data. Dropping and recreating clustered index didn't reclaim
> space ( I am running SQL 2000 sp4).
> I am planning to perform following steps:
> 1. BCP out data
> 2. Drop/recreate table with only clustered index
> 3. BCP in data
> 4. Create non clustered index, foreign key, constraints etc
> Do you see any potential problem with exporting such a big size to text
> file? I am planning to export the data to multiple files so that when I BCP
> in, I can load them concurrently and also planning to use ORDER and TABLOCK
> hint. Can I use ORDER and TABLOCK hint when loading data to a table with
> clustered index and loading multiple file simultaneously? I am guessing if I
> use "queryout" option with "order by" clause on clustered column when bcp
> out the data, I should be able to use ORDER hint when loading data in. Let
> me know if I am wrong. Also, I am planning to use SQL server Native format.
> If you have any experience in unloading/reloading very large table from sql
> server, I would love to hear your comments/suggestion.
>
>
sql