Need advise in designing a table

[bnath001]

Hi,

I am studying an old Access application and trying to re-engineer it.

In the old database application, they are using 2 tables.Both the tables have same table definition. one is a staging table and second one is production table.
They import data from an ascii file into the staging table and then delete all data from production table and append the records from staging table to production table. Could there be any specific beneficial reason for this kind of design in the tables? Why should we have two tables? Why can't we import directly into production table?

Any ideas please?

thank you
nath

 

[dawnd3]

I believe you will have no problem importing and appending to an existing table as long as the field names match. Otherwise you will need to do it as you are now. Dawn

 

[morrisdev]

it depends on how much is being appended. Even large SQL Server Apps like CommerceOne use stageing tables and then execute the update when processer time is available and the number of users is low.

Remember...you will still have to remove those existing records before the append. If the import fails, then you are pretty screwed.


-Dan

 

[Rhonin]

When importing data into a table, it is best to do it using a staging table especially if your app is in use. It is far easier to cull and modify the imported data in stage than in your main table. This is especially true if the number of records is high.

 

[ErikZ]

Yes, this is to prevent problems. If your impost fails and trashes the table, you're ok because your production table is untouched.