[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1108
  • Last Modified:

Truncate Multiple Tables with Dynamic SQL

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
begin
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
end
'
exec(@sSQL)
0
srejja
Asked:
srejja
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello srejja,

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

Regards,

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now