TRUNCATE multiple tables SQL Server 2014

Question

I want to truncate multiple tables. I know that it isn't possible in the same way that DELETE will delete the rows from multiple tables.

In this question truncate multi tables IndoKnight provides the OP-designated best answer. I want to try that. However, I get a syntax error at:

TRUNCATE TABLE @tableName

To troubleshoot I tried printing the variables because when I first tried using TRUNCATE TABLE I needed to include the database name and schema (e.g. NuggetDemoDB.dbo.tablename) to get it to work. I CAN print the variable @tableList. But I CANNOT print @tableName.

DECLARE @delimiter CHAR(1),
    @tableList VARCHAR(MAX),
    @tableName VARCHAR(20),
    @currLen INT

SET @delimiter = ','

SET @tableList = 'Employees,Products,Sales'
--PRINT @tableList

WHILE LEN(@tableList) > 0
BEGIN
SELECT @currLen = 
(
    CASE charindex( @delimiter, @tableList ) 
        WHEN 0 THEN len( @tableList  ) 
        ELSE ( charindex( @delimiter, @tableList  ) -1 )
    END
)

SET @tableName = SUBSTRING (@tableList,1,@currLen )
--PRINT @tableName
TRUNCATE TABLE @tableName

SELECT tableList = 
(
    CASE ( len( @tableList ) - @currLen  ) 
        WHEN 0 THEN '' 
        ELSE right( @tableList, len( @tableList ) - @currLen  - 1 ) 
    END
) 
END

Edit: Fixed the table list to remove the extra "Sales" from the list of tables and added "Employees".

You can't parameterize Identifiers. I'm kinda surprised nobody wrote this as a comment to the answer you linked to. You are going to have to either use a different Truncate statement for each table or dynamic sql. — Zohar Peled, Jun 19 '16 at 14:17
You need to use a little dynamic SQL to iterate through each table. — square_particle, Jun 19 '16 at 14:19
Actually, you can automate the table list. sys.objects and sys.tables are good starting points. Use QUOTENAME (, '[]') — clifton_h, Jun 19 '16 at 14:24

score 2 · Answer 1 · answered Jun 19 '16 at 15:03

Even thought Sales is listed twice... No harm

Declare @TableList varchar(max)
SET @tableList = 'Sales,Products,Sales'
Set @tableList = 'Truncate Table '+replace(@tablelist,',',';Truncate Table ')+';'
Print @TableList

--Exec(@tablelist)  --<< If you are TRULY comfortable with the results

Returns

Truncate Table Sales;Truncate Table Products;Truncate Table Sales

score 2 · Answer 2 · answered Jun 19 '16 at 21:48

First and foremost, you may want to consider spending a little energy to come up with a SQL implementation for splitting a string into rows, e.g. Split, List, etc. This will prove to be helpful not only for this exercise, but for many others. Then this post is not about how to turn a comma separated list into rows and we can then concentrate on the dynamic SQL needed in order to do what is needed.

Example

The below example assumes that you have a function named List to take care of transposing the comma separated list into rows.

declare
    @TableList varchar(max) = 'Sales, Products, Sales';

declare
    @Sql varchar(max) = (
        select distinct 'truncate table ' + name + ';'
        from List(@TableList)
        for xml path(''));

exec (@Sql);

One last thing about truncate of delete

Truncate will not work if you are truncating data where there is a foreign key relationship to another table.

You will get something like the below error.

Msg 4712, Level 16, State 1, Line 19
Cannot truncate table 'Something' because it is being referenced by a FOREIGN KEY constraint.

Nice answer, though there is no integrity check. Still, i feel we need to know who is using this proc and why. — clifton_h, Jun 19 '16 at 23:21

score 1 · Answer 3 · answered Jun 19 '16 at 16:11

Below is an example that uses a table variable instead of delimited list. If the source of your table list is already in a table, you could tweak this script to use that as the source instead. Note that the extra Sales table is redundant (gleaned from the script your question) and can be removed. The table names can be database and/or schema qualified if desired.

DECLARE @tableList TABLE(TableName nvarchar(393));
DECLARE @TruncateTableBatch nvarchar(MAX);
INSERT INTO @tableList VALUES
      (N'Sales')
    , (N'Products')
    , (N'Sales');

SET @TruncateTableBatch = (SELECT N'TRUNCATE TABLE ' + TableName + N'
'
FROM @tableList
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');

--PRINT @SQL;
EXECUTE(@TruncateTableBatch);

score 0 · Answer 4 · answered Jun 19 '16 at 19:02

0

What about something like:

exec sp_msforeachtable @command1 ='truncate table ?' ,@whereand = ' and object_id In (select object_id from sys.objects where name in ("sales", "products")'

Have not tested it yet. But it might give a useful hint.

answered Jun 19 '16 at 19:02

Sven

11
3

Additional: If you have a couple of foreign key violations while trying to truncate, you may use a script to generate create statements, store them to tempdb, delete the foreign keys, truncate and restore the foreign keys from tempdb. Otherwhise you may keep the correct order of the tables while truncating. You may have a look at "delete cascade" for foreign keys. Maybe it is interesting for you: https://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/ – Sven Jun 20 '16 at 08:01

TRUNCATE multiple tables SQL Server 2014

4 Answers4