Delete table with year and month

emi_sastra
emi_sastra used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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

Author

Commented:
Hi PortletPaul,

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

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

Thank you.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Please answer my question:  

Do you want to "truncate" each table? read the documentation first, url provided above
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
I want to use variable for table name.

That's why I want to use looping.

Thank you.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Author

Commented:
Just want to delete data as I code.

Thank you,

Author

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

Thank you.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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

Author

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.

Author

Commented:
Great.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial