Partition Switch to another database

  • is it possible to switch the data from one database to another. See the below test code

    USE [AdventureWorks2008R2]

    GO

    ALTER TABLE orders_part

    SWITCH PARTITION 1 TO TailspinToys.dbo.orders_part_2

    Error

    Msg 4948, Level 16, State 1, Line 1

    ALTER TABLE SWITCH statement failed. The source table 'orders_part' is in database 'AdventureWorks2008R2' while the target table 'TailspinToys.dbo.orders_part_2' is in database 'TailspinToys'.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • No. It's not possible.

    Not only in the same database, objects should be in the same filegroup.

    BOL:

    Source and target tables must share the same filegroup. The source and the target table of the ALTER TABLE...SWITCH statement must reside in the same filegroup, and their large-value columns must be stored in the same filegroup. Any corresponding indexes, index partitions, or indexed view partitions must also reside in the same filegroup. However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.

  • Suresh B. (1/16/2013)


    However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.

    What does it mean ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (1/16/2013)


    Suresh B. (1/16/2013)


    However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.

    What does it mean ?

    It means table and index can be reside on different filegroup.

    But they must move to thier corresponding filegroup.

    For example: Let's say Table1 is on filegroup Data1. And an index called IX_Table1_Name is on filegroup Index1.

    They should move to thier corresponding filegroup on the same database.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply