Staging Tables

3 4

Staging tables are temporary tables that you create to load data into SQL Server, to process and manipulate that data, and to copy that data into the appropriate table or tables within the database. In this section, you'll learn how and when to use staging tables.

Staging Table Basics

A staging table is a temporary storage area in the database into which you can copy data. You can then use T-SQL to process that data into the desired format by performing operations, such as joins, that involve the staging table and existing tables.

By letting you process data during the loading process, staging tables enable you to overcome certain limitations of data-loading methods. Most data-loading techniques allow the data to be simply copied into the database, without any processing. By using DTS, you can perform some data transformations, but you cannot make changes based on the data in the database. The main benefit of using staging tables is that they give you the ability to perform join operations based on information either in the staging table itself or in existing tables.

Using Staging Tables

In this section, we'll look at three examples of using staging tables: merging and loading a table, loading and splitting a table, and loading unique values into a table. These examples should help you understand how staging tables can benefit you when you load data into your database.

Merging and Loading a Table

Consider a table in a data mart that is a combination of two tables from online transaction processing (OLTP) systems. This table has columns A, B, C, D, and E; columns A, B, and C exist in one table, and columns C, D, and E exist in another table. The two input tables can both be staged, and a join operation can be used to load the table into the data mart. This operation is shown in Figure 24-22.

Figure 24-22. Using staging tables for joins.

Loading and Splitting a Table

The corollary to our first example is a scenario in which one table is being loaded into several tables in the data mart, which might occur for normalization purposes. This task can be easily accomplished by copying the data into the staging table and using two queries to load the staging table into the data mart tables, as shown in Figure 24-23.

Figure 24-23. Using staging tables for data splits.

Loading Unique Values into a Table

You can load unique values into a table by bulk copying the data to be loaded into a staging table and then using a T-SQL statement to insert data into the main table only if that data does not already exist in the table. This option is useful when you are loading data that might invalidate some business rules. To copy unique values from a staging table, use the NOT IN statement, as shown here:

 INSERT INTO table ( columnA, columnB ) SELECT columnA, columnB FROM staging_table WHERE columnA NOT IN ( SELECT columnA FROM table ) 

This rather convoluted T-SQL statement simply loads those rows from the staging table into the main table that do not have a columnA value that matches one in the main table, thus ensuring that no duplicate values will be inserted.