Could you please help me with a solution for dynamic partitioning in SQL Server including the creation of partition, splitting , Merging , Switching and archiving to the history table .
1.maintain 3 months in the current table .
2.older than 3 months , create,split,merge and switch the partition (partition switch in/switch out)
3. Move to history table older than 3 months
4. Archive and purge the data older than 5 years
All these steps should be performed dynamically in SQL SERVER. Please help me with the solution with an example source code. Your help is greatly appreciated.