none
ALTER TABLE SWITCH PARTITION SQL Server Syntax RRS feed

  • Question

  • Can someone please explain to me or point me to a good resource for explaining ALTER TABLE TableName1 SWITCH PARTITION 1 TO TableName2? And PLEASE not the Microsoft documentation...I hate trying to decipher through Microsoft documentation.

    Thanks in advance for your review and am hopeful for a reply.

    Friday, August 16, 2019 8:47 PM

All replies

  • ALTER TABLE [PartitionTable] SWITCH PARTITION [PARTITION_NUMBER] TO [NonPartitionTable];
    ALTER TABLE [PartitionTable] SWITCH PARTITION [PARTITION_NUMBER] TO [OtherPartitionTable] PARTITION [PARTITION_NUMBER];

    A Fan of SSIS, SSRS and SSAS


    Friday, August 16, 2019 9:38 PM
  • It's a pretty easy command, so which part of it do you need to be explained?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, August 17, 2019 5:11 AM
  • It's a pretty easy command, so which part of it do you need to be explained?

    Nah, there are some decently complicated rules that have to be fulfilled, and maybe Bobby are struggling with those. They are described in the topic for ALTER TABLE SWITCH, or in a topic referred to in that topic. I certainly do not know these rules by heart, although many of them are fairly obvious if you have some understanding of SQL Server.
    In any case, my plans for the day is not to do a write-up on partition switching, reading the Books Online topic for Bobbby. And what says that Bobby would find my text moder understandable than Books Online?

    So I echo Olaf's suggestion: tell us more clearly what you have problem with. Or Google around to see if you find something you find easier to digest.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, August 17, 2019 8:19 AM
  • Hi Bobby,

     

    I want to recommend one book which is really great when i was learning SQL Server--- Microsoft SQL Server 2012  Internals by Kalen Delaney. There're some details about your question. (from page 444 to page 452) 

    You could refer to that. 

     

    Sabrina 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 19, 2019 8:17 AM
  • The Source Table? The Target Table? and what does SWITCH PARTITION 1 mean??
    Monday, August 19, 2019 6:52 PM
  • I suggest you read "3. Archive data by switching out: Switch from Partition to Non-Partitioned":

    https://www.cathrinewilhelmsen.net/2015/04/19/table-partitioning-in-sql-server-partition-switching/


    Monday, August 19, 2019 7:10 PM
    Moderator