Dedicated SQL Pool: How to pass the range values dynamically in table partition

Seenuvasan, Venkatanathan 56 Reputation points
2023-05-11T17:36:52.91+00:00

We use azure dedicated sql pool (formely known as sql dw). In CTAS, partition is created so far like below. Range values (20190101,20200101, etc...) are hardcoded. Is it possible to pass the ranges dynamically ? I want to pass these date ranges from other tables / views.

Create TABLE AL.Time_Entry 
WITH 
(
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = HASH([Location]),
	PARTITION (
		[Date] RANGE RIGHT FOR VALUES (
						20190101,
						20200101,
						20210101,
						20220101,
						20230101,
					          )
		   )
)
AS
Select * from AL.Time
Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,553 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. BhargavaGunnam-MSFT 28,111 Reputation points Microsoft Employee
    2023-05-12T21:33:41.2+00:00

    Hello Seenuvasan, Venkatanathan,

    My understanding is, In Azure Synapse Analytics, you can't directly pass dynamic partition values in the CREATE TABLE AS SELECT (CTAS) statement.

    However, using a stored procedure to generate and execute the dynamic CTAS statement based on the date ranges from another table or view.

    Here is an example to create a SP to generate dynamic CTAS statement,

    CREATE PROCEDURE GenerateDynamicCTAS
    AS
    BEGIN
        DECLARE @PartitionValues NVARCHAR(MAX);
        DECLARE @CTASQuery NVARCHAR(MAX);
    
        -- Get the date ranges from another table or view
        SELECT @PartitionValues = STRING_AGG(CONVERT(NVARCHAR, DateValue), ',')
        FROM YourDateRangeTable;
    
        -- Build the dynamic CTAS statement
        SET @CTASQuery = N'
        CREATE TABLE AL.Time_Entry
        WITH
        (
            CLUSTERED COLUMNSTORE INDEX,
            DISTRIBUTION = HASH([Location]),
            PARTITION (
                [Date] RANGE RIGHT FOR VALUES (' + @PartitionValues + ')
            )
        )
        AS
        SELECT * FROM AL.Time;';
    
        -- Execute the dynamic CTAS statement
        EXEC sp_executesql @CTASQuery;
    END;
    

    Here, replace YourDateRangeTable with the table or view containing the date ranges you want to use for partitioning. After creating the stored procedure, you can execute it to generate the AL.Time_Entry table with dynamic partition values:

    EXEC GenerateDynamicCTAS;

    I hope this helps. Please let me know if you have any further questions.

    1 person found this answer helpful.