How to insert data using index unusable/rebuild

I am trying to rewrite a generic data load procedure for large tables to use the INDEX REBUILD statements instead of dropping and recreating the indexes. Trying to get rid of hard-coding the CREATE INDEX statements for less maintenance.

We do have some PK and UNIQUE indexes on the tables that need to be loaded. I figured out how to get around the PK indexes by disabling the PK constraints prior to doing the INDEX REBUILD, but don't see how to get around or disable the UNQUE INDEX being UNUSABLE when I need to insert the data. There is no contraint on the table, just the unique index.

Here is my basic processing for loading the tables.

1. truncate destination table.
2. Change status of indexes to UNUSABLE.
3. Generate index rebuild statements prior to disabling primary key constraints because after that the index info is not available.
4. SET SESSION SKIP_UNUSABLE_INDEXES=TRUE so that unusable indexes don't cause failure when inserting the data.
5. Disble any primary keys on the table because you can't do an index rebuild with an active PK constraint.
6. Insert data from source table (copying table data).
7. Enable any primary keys on the table that were disbled earlier.
8. Rebuild indexes.
9. Set SKIP_UNUSABLE_INDEXES=FALSE

Any Help greatly appreciated. Received on Thu Jul 10 2003 - 15:06:16 CDT