How to create Staging Tables

[Catadmin]

Help! I've read several references in my books and on this forum on creating staging tables, but I can't find anything that details how to actually do the creation of a staging table.

I have two tables I need to combine into one (but keep the original two tables) while doing a calculation on a field in each of the tables. I'm going to be creating a whole new record set based on a combined key in both tables. From what I've read, doing staging tables seems to be the correct way to go on this rather than create a new table for each of the originals (with the data calculation) and then combining the tables into a new third table.

However, as I said above, I can't find any reference specific to how to create the staging tables. Can anyone give me any hints? BOL doesn't contain a reference to Staging Tables or to Table, Stage (Staging).

Thanks!



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[gradley]

Staging tables ARE new tables usually based on the originals but often stored in a separate database.

We use staging tables to de-normalize data so that it is easier to feed into OLAP. We also use staging tables for ETL processing with interface data to make sure the data is in the proper format prior to updating the production database.

 

[Catadmin]

Well that explains why I can't find information on how to create them. @=) I've been searching for something different than "Create Table".

Thank you for clarifying this. I appreciate it. I may or may not need to use them for doing a series of calculations on fields in joined tables.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[gradley]

Anytime!

If you in fact need to create a table to perform your calculations for a temporary solution, you might try creating a temp table (beginning with a # sign) that goes away after the batch is complete.

You can also create views or Stored Prodedures that perform the calculations prior to retrieving the data.

Just food for thought.

 

[Catadmin]

I'm currently working with views. Unfortunately, when I got to use those views to update the new table the info is going into, I keep getting a 'derived table cannot be updated' error.

Which is weird because I'm not trying to update the views. I'm trying to use the views to update the table. So I've probably got bad code somewhere.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!