Faster way to clear a table

[DaveJHPT]

Hi all

I have a process in an inherited db that rebuilds a table of about 200,000 records every week. The build bit runs fairly quickly (considering), but clearing the table out first is taking 20+ minutes. It currently uses a deleted query ("DELETE * from tblTable") which I thought would have been the fastest way of doing it, but can anybody recommend anything quicker?

I've looked at other solutions such as only adding new data, but it gets very messy. I know it's all horribly unnormalised and stuff but I'm stuck with it unfortunately.

Dave

 

[Ron_dK]

Is the new data so different from the previous data that you need to delete all ( 200.000) records.
Can't you just add the records with data that have been altered/changed.
It would save you a lasting process of deletion and addition.

 

[DaveJHPT]

I'm afraid so - not just new data but historic data changes. I know this is precisely the sort of problem you get with duplicated data, but I can't change the design.

 

[david.brent]

Could you just drop (or delete) the old table and make a new one with the new data?

It sounds like the 'live' data is stored in another database. Is it an Access database? You might be able to copy the new table across and replace this historic one.

Let's know a few more details. Where is the 'live' data stored. How are you currently importing the live data into your 'historic' data table and anything else you might think is useful.

Cheers

 

[DaveJHPT]

I think the best way is going to be dropping the table completely and rebuilding from either some make-table stuff in SQL or copying from a blank template.

The data actually comes from an unnormalised table in the same db, so not only is there an unnormalised table, but the data is then duplicated on a normalised table. I can't change the design as there are loads of existing queries and reports which use both tables & I haven't got the time to do the job properly!