SwitchStagingTableIntoPartition.sql

/*
Purpose:
- Build and optionally execute the commands that switch a staging table into one partition.

Safety:
- Generates commands by default. Set @Execute = 1 only after validating identical table/index
  structures, an empty target partition, boundaries, constraints, and the maintenance window.

Requirements:
- SQL Server 2012 or later.
- ALTER permission on the source and target tables and partition function metadata access.

Customization:
- Review every variable below. The example names are deliberately neutral.
*/

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @SchemaName SYSNAME = N'dbo';
DECLARE @StageTable SYSNAME = N'SampleEvents_Stage';
DECLARE @TargetTable SYSNAME = N'SampleEvents';
DECLARE @PartitionFunction SYSNAME = N'SampleEventIdPartitionFunction';
DECLARE @PartitionKey SYSNAME = N'event_id';
DECLARE @LeftBoundary BIGINT = 40000;
DECLARE @RightBoundary BIGINT = 50000;
DECLARE @Execute BIT = 0;

DECLARE @PartitionNumber INT;
DECLARE @ConstraintName SYSNAME = N'CK_' + @StageTable + N'_SwitchRange';
DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N'SELECT @Result = $PARTITION.' + QUOTENAME(@PartitionFunction) + N'(@Boundary);';

EXEC sys.sp_executesql
    @Sql,
    N'@Boundary BIGINT, @Result INT OUTPUT',
    @Boundary = @LeftBoundary,
    @Result = @PartitionNumber OUTPUT;

SET @Sql =
    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@StageTable)
    + N' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(@ConstraintName)
    + N' CHECK (' + QUOTENAME(@PartitionKey) + N' >= ' + CONVERT(NVARCHAR(30), @LeftBoundary)
    + N' AND ' + QUOTENAME(@PartitionKey) + N' < ' + CONVERT(NVARCHAR(30), @RightBoundary) + N');'
    + CHAR(13) + CHAR(10)
    + N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@StageTable)
    + N' SWITCH TO ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TargetTable)
    + N' PARTITION ' + CONVERT(NVARCHAR(20), @PartitionNumber) + N';';

SELECT
    @PartitionNumber AS target_partition_number,
    @Sql AS command_to_review;

IF @Execute = 1
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        EXEC sys.sp_executesql @Sql;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;