SQL Server 2012 - Truncate table partitions

1. Create staging table [staging_TABLE_NAME]. The best way to create it is from SSMS 

ServerName -> Databases -> DatabaseName -> Tables -> TABLE_NAME (Right click) -> Storage -> Manage partition -> Create a staging table for partition switching

In the generated script comment ALTER TABLE lines with constraint on partition limits.

Execute script to create staging table.

Update in truncate partition script staging_TABLE_NAME with generated staging table name.

2. Get details about table partititons

DECLARE @TableName NVARCHAR(200) = N'TABLE_NAME'

SELECT SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(i.object_id) AS [object]
     , p.partition_number AS [p#]
     , fg.name AS [filegroup]
     , p.rows
     , au.total_pages AS pages
     , CASE boundary_value_on_right
       WHEN 1 THEN 'less than'
       ELSE 'less than or equal to' END as comparison
     , rv.value
     , CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
       SUBSTRING (au.first_page, 5, 1))) + ':' + CONVERT (VARCHAR(20),
       CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
       SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +
       SUBSTRING (au.first_page, 1, 1))) AS first_page
FROM sys.partitions p
INNER JOIN sys.indexes i
     ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.objects o
     ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au
     ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
     ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f
     ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
     ON dds.partition_scheme_id = ps.data_space_id
     AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
     ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values rv
     ON f.function_id = rv.function_id
     AND p.partition_number = rv.boundary_id
WHERE i.index_id < 2
     AND o.object_id = OBJECT_ID(@TableName)
order by 2;

3. Execute truncate script for selected partitions. To execute also the commands un-comment EXEC lines. Test it before execution in ptoduction like environment !!!!

DECLARE @partno nvarchar(5)
DECLARE @cmd1 nvarchar(200)
DECLARE @cmd2 nvarchar(200)
SELECT @cmd2 = 'TRUNCATE TABLE [staging_TABLE_NAME]'

DECLARE table_names CURSOR FOR
SELECT distinct partition_number -- partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TABLE_NAME'
and partition_number between 20 and 100
order by partition_number

OPEN table_names
FETCH NEXT FROM table_names INTO @partno
WHILE @@fetch_status = 0
BEGIN

IF EXISTS (SELECT partition_number -- partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TABLE_NAME'
and partition_number = @partno)

BEGIN

SELECT @cmd1 = 'ALTER TABLE [DBNAME].[dbo].[TABLE_NAME] SWITCH PARTITION ' + @partno + ' TO [DBNAME].[dbo].[staging_TABLE_NAME]'
   
PRINT @cmd1
PRINT @cmd2
-- EXEC sp_executeSQL @cmd1;
-- EXEC sp_executeSQL @cmd2;
END

FETCH NEXT FROM table_names INTO @partno
END

CLOSE table_names
DEALLOCATE table_names