• I probably wouldn't use an iterative approach here.  Since the tables are empty, I'd add the correct Clustered Index to the table, set the database to the BULK LOGGED Recovery Model, and use "Minimal Logging" to do the inserts (which necessarily requires that the inserts be done in the same order as the clustered index).

    Without a Clustered Index, the inserts will be approximately twice as fast because of the greatly reduced need for logging.  But, then you'd need to add the Clustered Index, which would temporarily double the footprint of the large table in the MDF and leave empty space in the MDF equal to about 120% of the largest table.  It would also take a comparatively very long time.

    With the Clustered Index in place and using the correct minimal logging technique, you'll end up with a table with the Clustered Index already in place, very little unwanted freespace in the target database, and it'll take much less time than building the Clustered Index separately.

    And, no... there's no way that I'd use SSIS for any of this.  It's just not necessary.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)