1

I would like to switch out a partition from a daily partitioned table and merge it into monthly partitioned table. I am unsure as to how to go about it. Here's what I have so far.

I have two tables, Event_Daily and Event_Archive_Monthly. Both tables are partitioned on CreatedDate datetime with Event_Daily being partitioned by Day and Event_Archive_Monthly being partitioned by Month.

Event_Archive_Monthly has the following file groups:

  • Archive_Monthly_2018_05_01_FG
  • Archive_Monthly_2018_06_01_FG
  • Archive_Monthly_2018_07_01_FG

Event_Daily has the following file groups:

  • Daily_L_NULL_FG
  • Daily_2018_06_18_FG
  • Daily_2018_06_19_FG

I have 9 records in Event_Daily in the 2018_06_18 file group and no data on the other partitions. I have created a SwitchOut table on Daily_2018_06_18_FG and have issued these commands to switch out the data and merge the boundary point:

PRINT 'switching out previous days data'

ALTER TABLE [dbo].[Event_Daily]
SWITCH PARTITION 2 TO dbo.Event_Daily_SwitchOut;
GO

PRINT 'merging boundary point'

ALTER PARTITION FUNCTION Daily_PF ()
    MERGE RANGE ( '2018-06-18' )
GO

At this point I am unsure as to how to get this data into the archive table. Ideally, after everything is said and done the file group Daily_2018_06_18_FG is gone. One option I know I have is to just insert the data and then drop the SwitchOut table and drop the file group.

Is there a way, using the Partitioned Table, to merge the SwitchOut table that is on the filegroup Daily_2018_06_18_FG into the partition on Archive_Monthly_2018_06_01_FG?

1

Community wiki answer:

Is there a way, using the Partitioned Table, to merge the SwitchOut table that is on the filegroup Daily_2018_06_18_FG into the partition on Archive_Monthly_2018_06_01_FG?

No, you'll need to use INSERT...SELECT for row processing. MERGE/SPLIT/SWITCH DDL are partition level.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.