• Grant Fritchey (3/18/2013)


    If you can make changes to your architecture, you might want to look into partitioning the data.

    You can't switch partition to a switching table that is in different database. Switching table and partition must reside on the same filegroup in order for switch partitions to work.

    The only benefit would be much faster delete (drop partition) of the data you already copied - worth using.

    I would do a partition switch, bcp-out switching table to a file in native format, bcp-in that file to destination db switching table, and then switch that into destination partitioned table. That is by far the fastest method.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths