Delete table with year and month

Hi All,

I try to delete table data. Please see below :

WHILE ( @IntMonth <= 12)
BEGIN
    DELETE FROM 'TDJURNAL' + ' + @Year + ' + @IntMonth

      SET @IntMonth = @IntMonth + 1
END

For example table name TDJURNAL201701 to 201712.

How could I do it ?

Thank you.
LVL 1
emi_sastraAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
I really don't know how to help - fully - if I don't understand the nature of those deletion.

So I shall have to leave that to you to sort out. I assume below that you will truncate the tables.
declare @Year as int;
declare @intMonth as int;
declare @maxMonth as int;

set @Year = 2017
set @intMonth = 1
set @maxMonth = 5

declare @query as varchar(max);

WHILE ( @IntMonth <= @maxMonth)
BEGIN
   
   set @query = 'TRUNCATE TABLE TDJURNAL' + cast(@Year as varchar(4)) + right( '00' + cast(@IntMonth as varchar(2)),2)
   
   select @query
   execute(@query)
   
   SET @IntMonth = @IntMonth + 1
END

Open in new window


tested at: http://rextester.com/EPTM90228
2
 
PortletPaulfreelancerCommented:
NO!

you won't get "leading zeros" to start with e.g. 1 <> "01"

and you cannot inject a table name into SQL through a variable like that, you need "dynamic sql" to do that.

Do you want to "truncate" each table?
https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql
0
 
emi_sastraAuthor Commented:
Hi PortletPaul,

    DELETE FROM 'TDJURNAL' + ' + @Year + ' + @IntMonth

Would you please how to code correctly for the above statement ?

Thank you.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PortletPaulfreelancerCommented:
Please answer my question:  

Do you want to "truncate" each table? read the documentation first, url provided above
0
 
emi_sastraAuthor Commented:
I want to use variable for table name.

That's why I want to use looping.

Thank you.
0
 
PortletPaulfreelancerCommented:
sigh....

Please answer my question:  

Do you want to "truncate" each table? read the documentation first, url provided above

only then can I provide more... I need to know if you want to truncate those tables, that is why I asked the question.
0
 
emi_sastraAuthor Commented:
Just want to delete data as I code.

Thank you,
0
 
emi_sastraAuthor Commented:
From table TDJURNAL201701, XXXX201702 and etc.
Using looping or any other better way.

Thank you.
0
 
emi_sastraAuthor Commented:
- I really don't know how to help - fully - if I don't understand the nature of those deletion.
Yes, it is the right way.

Great. It works.

Thank you very much fo your help.
0
 
emi_sastraAuthor Commented:
Great.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.