Home Forums Data Warehousing Integration Services Loading to table with clustered PK / Order by in source RE: Loading to table with clustered PK / Order by in source

  • March 27, 2015 at 5:56 am

    #1786821

    Samuel Vella (3/27/2015)

    Bulk loads are quickest for tables which are either empty or have no indexes.

    If you are inserting a lot of data into an already large table then rebuilding the indexes and PK after a load might not be a good use of CPU resources.

    I assume from the nature of your question that the new data your loading is appended to the clustered PK and wouldn't include any data that falls in between existing PK values.

    If this is the case it might be worthwhile investigating partitioning strategies. With partitioning you can quickly bulk load into a empty table and then swap the newly populated table in for the next empty partition.

    I am loading empty work tables in a DW from a staging database. As all of the data is within SQL Server, sorting the source data is trivial. After this load is complete, I MERGE the data into the target tables. I need the PK to be in place to assist the MERGE.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.