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:
Event_Daily has the following file groups:
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?