Database Reference
In-Depth Information
Update DimCustomers
Set DimCustomers.CustomerName = Customers.CustomerName
From DimCustomers
JOIN Customers
On DimCustomers.CustomerId = Customers.CustomerId
AND RowStatus = '
u
'
-- Synchronize Deletes
Delete DimRows
From DimCustomers as DimRows
JOIN Customers
On DimRows.CustomerId = Customers.CustomerId
AND RowStatus = '
d
'
-- After we import data to the dim table
-- we must reset the flags to null!
Update Customers Set RowStatus = null
COMMIT TRANSACTION
-- Step #9. Test that both tables contain the same rows
Select * from Customers
Select * from DimCustomers
Go
-- Step #10. Setup an ETL process that will run the Synchronization code
As you can see, creating SQL code to accomplish incremental loading can be quite complex. The good news
is that you will not need to do this for most tables. Many tables are too small to benefit from the incremental
approach, and in those cases, you should try to keep your ETL processing as simple as possible and stick with
the flush and fill technique. For example, all the tables in our three demo databases have small amounts of data;
consequently, this topic focuses on the ush and ll technique for all the tables.
■
The problem with the approach we just demonstrated is that the oLTP table needs to have a tracking
column. since sQL 2005, Microsoft has introduced the sQL Merge command that performs these same comparison
tasks without a tracking column. we use sQL statements in Listing 6-3 as an example of an original method that will
work with most database software, but remember that there is more than one way to hook a fish. Although we don't
want to confuse readers by introducing multiple ways to solve the same tasks, we have created a web page detail-
ing a number of historic and modern approaches to this task. For more information, visit
www.NorthwestTech.org/
Note
Isolating the Data to Be Extracted
We now need to examine the data needed for the ETL process. Selecting all the data from the table you are
working on is a good start. You can do this by launching a query window, typing in a simple
SELECT
statement,
and executing it to get the results. We begin the process with a statement such as the one shown in Listing 6-4.
Listing 6-4.
Selecting All the Data from the Source Table
Select * from [Pubs].[dbo].[Titles]
