How to divide the table by month ("Both" and "Month") and automatically create monthly sections?

Question

I am trying to split the table by Year and Month . The column I'm breaking through is a datetime column with the ISO format ('20150110', 20150202 ', etc.).

For example, I have sales data for 2010, 2011, 2012. I would like the data to be broken down for a year and broken down for a month every year. (2010/01, 2010/02, ... 2010/12, 2011/01, ... 2015/01 ...)

EX:

Sales2010Jan, Sales2010Feb, Sales2011Jan, Sales2011Feb, Sales2012Dec, etc.

My question is: is this possible? If so, how can I automate the process using SSIS?

Answer 1

SSIS is ETL (Extract, Transform, Download). This is not what you want to do. You just need to dynamically create DDL statements.

I work a quarter below, but it also works with 1, 2 or X months if you want.

If you want to partition a table, you must first create a file, filegroups and a partisan table and manually set up partitioning

Creating N + 1 partitions for Q1 2015 (before, Q1, and after Q2) in a table with identifier int PK and partitioned column datetime2. Update it to add months, do it monthly or whatever you need ...

• First create groups of N files:

  Alter Database [Test] Add Filegroup [Part_Before2015] Go Alter Database Test Add Filegroup [Part_201501] Go Alter Database Test Add Filegroup [Part_201504] Go 

• Add a file for each filegroup:

   Alter Database [Test] Add FILE ( NAME = N'Part_Before2015', FILENAME = N'...\Part_Before2015.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_Before2015] Alter Database [Test] Add FILE ( NAME = N'Part_201501', FILENAME = N'...\Part_201501.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201501] Alter Database [Test] Add FILE ( NAME = N'Part_201504', FILENAME = N'...\Part_201504.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201504] 

• Create a function of splitting into datetime2 type (or date or even date and time):

   Create Partition Function RangePartFunction (datetime2) as Range Right For Values ('20150101', '20150401') 

• Create a partition scheme using the split function into each filegroup (N + 1):

   Create Partition Scheme RangePartScheme as Partition RangePartFunction To ([Part_Before2015], [Part_201501], [Part_201504]) 

• Create a partitioned table in the partition diagram:

   Create TABLE [PartitionTable] (id int identity(0, 1) not null, date datetime2 not null, text char(8000)) On RangePartScheme (date) ; 

• Add a clustered index to the partitioned column and partition scheme:

   Create Clustered Index IDX_Part On dbo.PartitionTable(date) On RangePartScheme (date); 

• Add PK to id column:

   Alter Table dbo.PartitionTable Add COntraint PK_Part Primary Key Nonclustered(id, date); 

Create a query used to add additional file groups after the right border and split the last section

• View Partition Schema Extensions and Partition Partition Separation
• Overview of DMV Used

• See it all and how to use it to create dynamic SQL

   Declare @currentDate datetime2 Declare @endDate datetime2 = '20160701' -- new end date Declare @dateAdd int = 3 -- Add 3 month = 1 Quarter -- Get Current boundaries Select @currentDate = DATEADD(MONTH, @dateAdd,Cast(MAX(value) as datetime2)) From sys.partition_range_values as r Inner Join sys.partition_functions as f on r.function_id = f.function_id Where f.name = 'RangePartFunction' -- Get all quarters between max and end date ; with d(id, date, name) as ( Select 0, @currentDate, Convert(char(6), @currentDate, 112) Union All Select id+1, DATEADD(MONTH, @dateAdd, date), Convert(char(6), DATEADD(MONTH, @dateAdd, date), 112) From d Where d.date <= @endDate ) Select * From ( Select id = id*10, query = 'If Not Exists(Select 1 From sys.filegroups Where name = ''Part_'+name+''') Begin Print ''Create Filegroup [Part_'+name+']'' Alter Database [Test] Add Filegroup [Part_'+name+'] End GO' From d Union All Select id*10+1, 'If Not Exists(Select 1 From sys.sysfiles Where name = ''Part_'+name+''') Begin Print ''Create File [Part_'+name+'.ndf]'' Alter Database [Test] Add FILE ( NAME = N''Part_'+name+''', FILENAME = N''C:\DB\MSSQL11.MSSQLSERVER\MSSQL\DATA\Part_'+name+'.ndf'' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_'+name+'] End GO' From d Union All Select id*10+2, 'Print ''Add Range [Part_'+name+']'' Alter Partition Scheme RangePartScheme Next Used [Part_'+name+'] Go' From d Union All Select id*10+3, 'Print ''Split Function ['+Convert(char(8), date, 112)+']'' Alter Partition Function RangePartFunction() Split Range ('''+Convert(char(8), date, 112)+'''); Go' From d ) as q order by id 

the output of this query is a list of SQL queries that must be executed in order.

Run dynamic SQL

• It can be done manually (copy and skip to SSMS)
• It can be executed in a while loop or with a cursor that will execute each row of the output table one at a time (use sp_executesql)

Automation

• Create a SQL Server job that emits SQL queries: run the query used to create dynamic SQL, save its output in a table variable, and then execute each statement with a loop / cursor

If you want to run it monthly and make sure that it is always created over the next 12 months, use this Set @endDate = DATEADD(MONTH, 12, getdate())

Finally

• It will print 4 * N lines for N missing quarters between the last border of the function and @endDate:

  • Create filegroup
  • Create file in filegroup
  • Extend Scheme Partition Range
  • Split a range of section function

• You can run it line by line with the cursor or while loop, or you can just copy and paste it into SMSS.

• It can be automated with a task, i.e. @endDate = DATEADD(MONTH, 3, getdate() will create the next 3 months
• Change @dateAdd to 1 if you want monthly sections
• Add your own columns or checks.

Link

Create task = https://www.mssqltips.com/sqlservertip/3052/simple-way-to-create-a-sql-server-job-using-tsql/

sp_executesql = https://technet.microsoft.com/en-us/library/ms188001%28v=sql.110%29.aspx

While loop = https://dba.stackexchange.com/questions/57933/can-exec-work-with-while-loop-of-cursor