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?
