Database Reference
In-Depth Information
An additional benefit of truncation over deletion is that if you have a table using the identity option to create
integer key values, truncation will automatically reset the numbering scheme to its original value (typically 1).
Deletion, on the other hand, will not reset the number; therefore, when you insert a new row, the new integer
value will continue from where the previous insertions left off before deletion. Normally this is not what you
want, because the numbering will no longer start from 1, which may be confusing.
Listings 6-1 and 6-2 are examples of code used in the flush and fill process. But, if you choose to do an
incremental load, do not clear the tables first. Instead, compare the values between the source and destination
tables. Then either add rows to the destination tables where new rows are found in the source, update rows in the
destination that are changed in the source or delete rows from the destination that are removed in the source.
In the example in Listing 6-3, a Customer's OLTP table contains a flag column called RowStatus. Each time
a row in this OLTP table is added, updated or marked for deletion, a flag is set indicating the operation. The flags
are examined to determine which data in the Customers table needs to be synchronized in the DimCustomers
table. Then an
INSERT
,
UPDATE
or
DELETE
takes place depending on the flag found in the table.
■
Tip
we have included this code in the
Chapter06
folder of the downloadable files if you would like to test it.
Listing 6-3.
Synchronizing Values Between Tables
Use TEMPDB
Go
-- Step #1. Make two demo tables
Create Table Customers
( CustomerId int
, CustomerName varchar(50)
, RowStatus Char(1) check(RowStatus in ('
i
','
u
','
d
') ) )
Go
Create Table DimCustomers
( CustomerId int
, CustomerName varchar(50) )
Go
-- Step #2. Add some starting data
Insert into Customers (CustomerId, CustomerName, RowStatus )
Values(1, 'Bob Smith', '
i
')
Go
Insert into Customers (CustomerId, CustomerName, RowStatus )
Values(2, 'Sue Jones', '
i
')
Go
-- Step #3. Verify that the tables are not synchronized
Select * from Customers
Select * from DimCustomers
Go
-- Step #4 Synchronize the tables with this code
BEGIN TRANSACTION
Insert into DimCustomers
(CustomerId, CustomerName)
Select CustomerId, CustomerName
From Customers
