Need a script to delete any records which has a specific timestamp

Question

Post reply

Need a script to delete any records which has a specific timestamp

John Paul-702936

SSCrazy

Points: 2182
More actions
July 1, 2009 at 12:38 pm

#93765

hi ,
need a Script that

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

John Paul (7/1/2009)
hi ,
need a Script that
delete any records which has a timestamp .

Extremely vague request, extremely vague response:

delete from ATable where TimeStamp = 'somedatetime';

If you want a better answer, give us a better question.

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 2

Sorry for that ....

I have a number of tables which keeps on updating from last 20 years..

My question is

i want to delete the records which updated before 2000

I think , now you can understand

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Delete from SomeTable WHERE SomeDateColumn < '2000/01/01'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 4

But , i don't have any coulmn for Date ..

actually , the scenario is we have a tables (very large) we want to delete the records that entered before 2006 into the table ,

we don't have any column that hold the Dateof insert

some thing like @rowversion ( I am not sure)

1) Need to find the column entry before 2006

2) Delete the record

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Without seeing the structure of the tables and some sample data, we can't really help you. There are no internal columns on the tables that will tell you when a record was entered into a table. If there isn't an actual datefield that has the entry date, there isn't much that can be done without really analysing your database and the data.

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 6

Thanks ... sorry for troubleing you again and again ..

what is the timestamp ... can we use this ....

please execuse me , if anything wrong in question ...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

delete from ATable where TimeStamp = 'somedatetime';

what is the timestamp ... can we use this ....

Lynn was referring to whatever column it is that stored the date inserted. If you have no column that says when a row was inserted, then there's no way that you can delete based on the date the row was inserted. SQL doesn't keep track of that behind the scenes.

we don't have any column that hold the Dateof insert

Then you cannot delete based on the date inserted.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LutzM SSC Guru Points: 107049 More actions · Answer 8

Just out of curiosity:

Let's assume you'd delete the first 75% ordered by primary key (hoping there is one).

How would you recognize that you've deleted too many rows? Let's say you've deleted half the data from 2006, too, how would you know?

Maybe that's a way how to figure out which rows can be deleted...

Not much more to say without table def and sample data....

EDIT:

Maybe that's a way how to figure out which rows can be deleted...

Note: Comment made in the meaning of "to think about the effect" and NOT to "try and see"!!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

lmu92 (7/1/2009)
Let's assume you'd delete the first 75% ordered by primary key (hoping there is one).

and assuming it's an identity.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass