All Forums Database
thnkgreen 2 posts Joined 06/16
11 Jul 2016
Faster way to delete and rebuild a table

Hello everyone.  This is my first post.  I have a question about deleting and rebuilding tables.  I have inherited some code that is structured like
1.  create an empty table in the sandbox  (I'll call it table A)
2.  insert all data from an existing table (I'll call it table B) to table A
3.  drop table B
4.  create table B again with no data in it (table structure only)  (I'll call this table B new)
5.  insert all data from table A into table B new
6.  drop table A
7.  add new month's data to table B new
 
I am wondering if it is really necessary to have so many steps?  My thinking is that there is another way to keep a safety copy of the data and eliminate some of the steps, like 1 and 2.  I could be totally wrong here but does this current way of doing things seem like the best (i.e. fastest) way?  Thank you

Tags:
Fred 1096 posts Joined 08/04
11 Jul 2016

So both step 2 and step 5 insert ALL the data (no WHERE clause)? And table A is dropped before applying the new data to B? (which would mean it's not really a "safety copy")
Why not skip 1 through 6 and just insert the new data?

M.Saeed Khurram 544 posts Joined 09/12
12 Jul 2016

Hi,
Even if you skip all the steps and do only step 7, the result will be same.

Khurram

thnkgreen 2 posts Joined 06/16
12 Jul 2016

Thanks for your replies.  To answer your question Fred, table A is dropped after it is copied to table B new (step 5).    What is really happening is the original table B is being appended to.  Step 2 backs up table B (temporarily) as table A, table B is then dropped, an empty copy of table B is created (table B new), the data is put back in from table A, table A is dropped, then a query is ran to get the new month's data and append it to table B new.  Just seemed like a few unncecessarys steps in there, but I could be wrong.  Thanks.  

M.Saeed Khurram 544 posts Joined 09/12
12 Jul 2016

Hi,
Thank you for clarification, but these all first 6 steps will lead the table B in same state as it is in initial state. So you can directly add the new data to table B without these steps. Just comment out these steps and execute only step 7. the result will be same.
Br,
Khurram

Khurram

You must sign in to leave a comment.