Delete Rows from Table

Question

Delete Rows from Table

mlimp

Say Hey Kid

Points: 708
More actions
June 20, 2008 at 10:17 am

#122835

Hi,
This is an easy one.
How do delete rows from a table?
I want to delete all the rows from the last month of a table so i have to be able to pass the month parameter to the delete statement.
Help.. plz.. i think this is an easy one

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

VRT SSCertifiable Points: 5776 More actions · Answer 1

mlimp (6/20/2008)
Hi,
This is an easy one.
How do delete rows from a table?
I want to delete all the rows from the last month of a table so i have to be able to pass the month parameter to the delete statement.
Help.. plz.. i think this is an easy one

I think for this, you can use the trigger. Why did you pose this question?

you want to create a SSIS package for this? I don't think it requires.

If at all you want create the SSIS package, you can use the delete stmt with where clause. Use Execute SQL task and pass the parameter to that.

Thank You

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

mlimp (6/20/2008)
Hi,
This is an easy one.
How do delete rows from a table?
I want to delete all the rows from the last month of a table so i have to be able to pass the month parameter to the delete statement.
Help.. plz.. i think this is an easy one

Heh... I love it when people say it's "an easy one" but still have to ask the question. 😀 (Just a little Friday night cynicism...)

You say you want to delete all the rows from the last month... but you don't really define what the last month is... do you mean the last 30 days? Do you mean that since this is June, you want to delete all of May? Or do you really need the month parameter so that you can determine which month, even if it isn't the last one, to delete?

I also have to say that this is a very unusual request... most people want to delete all rows EXCEPT the last month. Are you sure that's not what your really want to do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mlimp Say Hey Kid Points: 708 More actions · Answer 3

Jeff, venki, I'm going to ilustrate a little bit better my scenario so you guys can understand.

This is a Sales Table with Date,ProductId and GrossSales as column (the important ones for this example)

The table has 60+ million rows. I need to add about 200K rows daily from SAP but at the end of the month the amount of the GrossSales column may change. Therefore i'm going to have to do an update for this rows.

I do not want to do an update in a 60 million rows table for various reasons:

First: Reading from SAP daily takes a while.

Second: Updating from 60million rows table also takes some time

Third and more important: I've read about some time limitations on SSIS for working with big tables.

So this is what im doing:

Im inserting all the daily sales to a Temp Table (So i can see the history sales with the active month with a view and an union).

At the start of the next month i'm deleting all the last month's sales from the Temp Table and reading again all the last month's sales from SAP (which will have the correct GrossSales) but inserting it directly to the History Table.

The thing is i can only do this after the 5th day of the next month because that's when the Sales at SAP are correct. Therefore i will have to specify in SSIS to the delete statement to only delete last month's sales from the TEMP table since i will already have rows for the active month (5 days).

That's why i want to be able to delete last month sales.

I want to be able to send this via a parameters because there's a couple of things i want to be able to do like doing this proccess but for deleting only the last week sales (where i need to calcule what was the first day of last week).

Venki, i was able to this with the Execute SQL Statement but w/o parameter with the fixed statement: delete from table where month = month(getdate()) -1

Any suggestion on another way for doing this process will also be welcomed.

mlimp Say Hey Kid Points: 708 More actions · Answer 4

Somebody gave me this link that answered my question:

http://technet.microsoft.com/en-us/library/ms141138.aspx

Just wanted to share with you guys.

Still, if someone thinks there's a better way for doing what im trying to do i will apreciate it.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Sound like you've got a pretty good plan...

Deleting from last month is easy... Let's change the definition just a bit to make this even more simple... you want to delete everything before the first of the current month. So, the following should do just fine (don't ever test something like this on production data... test on a copy only).

DELETE yourtable

WHERE datecolumnname < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

The forumula in the WHERE clause finds the first of the current month. Try it with the following code...

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mlimp Say Hey Kid Points: 708 More actions · Answer 6

Jeff, your suggestion definitely does the work better. Thanks a lot.

Now im off to the second part of my problem. I posted it at http://www.sqlservercentral.com/Forums/Topic522743-148-1.aspx

If you could give it a look i will appreciate it.