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