• Typically it's done like this:

    Load new data into staging

    Add a new partition to main

    Switch new partition in main and staging (now staging is empty)

    Switch partition containing old data with staging

    Merge empty partition in main table

    Truncate staging.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass