CODEWITHSUNDEEP
sqlsql-server
Sumit Tiwary
Sumit Tiwary

Reputation: 29

How to Truncate a Table with Partitions?

I am trying to truncate a table using partitions.

SET @Sql =( 'TRUNCATE TABLE _result.result
WITH (PARTITIONS (SELECT PartitionNumber FROM #temp2 ))
GO');
Exec sp_executesql @sql

And,I am getting this Error.

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

In, my temp table there are so many PartitionNumber that's why I am using select statement with Partitions.

Upvotes: 0

Views: 1756

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You will need to construct the statement without a nested select:

SET @SQL = '
TRUNCATE TABLE _result.result
WITH (PARTITIONS [partitions])';

SET @SQL = REPLACE(@SQL, '[partitions]',
                   STRING_AGG(PartitionNumber, ', ')
                  )
FROM #temp2;

Exec sp_executesql @sql

Upvotes: 2

Related Questions