Truncate partitioned table with values from partitioned column?

I have partitioned a table on a column with varchar datatype.

The documentation on truncate table with partitioning uses the partitionids:

Truncate table parttable with (partitions (1,2,5))

I only have a list of the actual values from the partitioned column.

How can I get the partitionids from a list of values so I can use it in the truncate table statement?

Background:

I’m still very new to partitioning and need to validate my plan.

I have a fact table that is partitioned on a column with varchar datatype.

I have an etl process that loads data fully in the beginning of the month.

The data loads after that only contain a subset (the partitioned column) and needs to fully replace the existing data in the fact table.

My plan is:

-
Load the data first to a staging table.

-
Truncate the fact table on the partitioned values in the staging table.

-
Insert the data in the staging table to the fact table.

Am I doing this correctly?

Like this:

create partition function pf(int) as range right for values (1,2,3,4,5)
create partition scheme ps as partition pf all to ([Primary])

create table parttable(id int primary key, a int, b int, c int) on ps(id)

insert into parttable(id,a,b,c) values (0,0,0,0), (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6)

Truncate table parttable with (partitions ($partition.pf(1),$partition.pf(2),$partition.pf(5)))

select * from parttable

outputs

id          a           b           c
----------- ----------- ----------- -----------
0           0           0           0
3           3           3           3
4           4           4           4

And you can do this dynamically like this:

declare @listOfPartitionColumnValues nvarchar(max) = '1,2,5'
declare @listOfPartitionNumbers nvarchar(max) = 
  ( 
    select string_agg(pn,',')
    from
    (
      select distinct $partition.pf(v.value) pn
      from string_split(@listOfPartitionColumnValues,',') v
    ) p
  )
declare @sql nvarchar(max) = concat('truncate table parttable with (partitions (',@listOfPartitionNumbers ,'))')

print @sql
exec (@sql)

StackExchange Database Administrators Q#259724, answer score: 5