Truncate Multiple Tables with Dynamic SQL

Posted on 2009-04-29
Last Modified: 2012-06-21
I used the script below in an attempt to truncate a series of tables (in reality I'm attempting to truncate over 200 tables, I limited this example to 3).  Any ideas what I'm doing wrong?

The dynamic SQL should create a series like this and then execute:
truncate table DELETEMEtestbl1
truncate table DELETEMEtestbl2
truncate table DELETEMEtestbl3

Here's my botched attempt:
create table DELETEMEtestbl1 (AcctNum int)
create table DELETEMEtestbl2 (AcctNum int)
create table DELETEMEtestbl3 (AcctNum int)

declare @sSQL varchar(8000)
set @sSQL='
declare @counter int
set @counter=1
while @counter<3
declare @counter2 varchar(3)
set @counter2=convert(varchar(3),@counter)
declare @TableName varchar(60)
set @TableName=''DELETEMEtestbl''+@counter2
truncate table @TableName
set @counter=@counter+1
Question by:srejja
    LVL 75

    Accepted Solution

    Hello srejja,

    declare @sSQL varchar(8000)
    declare @counter int
    set @counter=1
    while @counter<=3
          declare @TableName varchar(60)
          set @TableName='DELETEMEtestbl'+convert(varchar(3),@counter)
          EXEC('truncate table '+@TableName)
          set @counter=@counter+1


    LVL 75

    Expert Comment

    by:Anthony Perkins
    Or simply:
    'IF EXISTS(SELECT 1 FROM sys.objects WHERE Type = ''U'' And Name LIKE ''DELETEMEtesttble_'')
          TRUNCATE TABLE [?]

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now