Hi Guys,
I've been a project to move data to staging tables. In the past what we have done is created flat files (from 50k to 2gb) from one database and placed them in a directory so a second database could pick them up.
The company now wants this data going into staging tables instead of a flat file, once this data has been loaded into the second database we delete the file after a 1 month from the directory.
1. What would be the best strategy would these be database staging tables or global temporary staging tables? Would it be best to use partitioned tables where I could delete the partitions after a month?
2. These tables will only be inserted into by first database and selected from the second database, so what structure of the table would be best?
3, what other performance techniques could i use to make this whole process quicker (i.e loading into the staging tables).
Is there any information on this or a white paper on how to look at this situation. has a similar company done this before.
thanks in advance