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

Question

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

Answer 1

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.