Need advise in designing a table

[bnath001]

Hi,

I am studying an old database 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

 

[tlbroadbent]

Most of the time we import directly to the production table if the entire table is being replaced. I can think of several reasons to import to a staging table first.

1- If the import fails, the process could skip the delete, leaving the production table intact.

2- The import process needs to convert, scrub or otherwise manipulate the imported data prior to appending to the production table. With newer versions of SQL Server, DTS allows much of this type of manipulation to occur during the import.

3- The staging table could have the same structure as the production table but lack the indexes and/or constraints. This would allow imports to proceed without failure even if the import file contains duplicate or invalid data. The process could then validate the data prior to appending to the production table. With the advent of DTS, much of this validation can be done upon import from the text file. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.