Empty tables specified from a list of table names without using a cursor

I have temporary data that is being uploaded from an external source into empty SQL Server tables. But not always the same tables. I need these tables to be cleared after a MERGE procedure is run.

The tables aren’t large and there isn’t a lot of data being populated at any one time. I can only seem to do this by using a cursor.

The following code works, but there must be a better way of doing this?

CREATE TABLE #Temp_TBL (name NVARCHAR(max));

INSERT INTO #Temp_TBL (name) 
SELECT 
    t.name 
FROM 
    SYS.DM_DB_PARTITION_STATS s 
INNER JOIN 
    sys.tables t ON t.[object_id] = s.[object_id] 
WHERE 
    s.row_count != 0 
    AND t.type_desc = 'USER_TABLE'

DECLARE @tableName nvarchar(max);

DECLARE cur CURSOR LOCAL FOR
    SELECT name FROM #Temp_TBL

OPEN cur

FETCH NEXT FROM cur INTO @tableName

WHILE @@FETCH_STATUS = 0 
BEGIN
    EXEC('TRUNCATE TABLE '+ @tableName); 
    FETCH NEXT FROM cur INTO @tableName
END

CLOSE cur
DEALLOCATE cur

DROP TABLE #Temp_TBL

You can use STRING_AGG to aggregate the whole thing into one batch.

You also need the schema name and you need to ensure names are quoted correctly using QUOTENAME.

Note that you should turn the join into an EXISTS so that you are checking if there are any partitions that contain rows, in case the table is partitioned.

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(
  'TRUNCATE TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';', '
')
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE EXISTS (SELECT 1
    FROM sys.dm_db_partition_stats p
    WHERE t.object_id = p.object_id
      AND p.row_count != 0
);

PRINT @sql;   -- your friend

EXEC sp_executesql @sql;