Chapter 59. Incremental loads using T-SQL and SSIS

 

Andy Leonard

Back in the old days, when we used to walk to the card punch centers barefoot in the snow uphill both ways and carve our own computer chips out of wood, people performed incremental loads using the following steps:

1.  

Open the source and destination files.

2.  

Look at them.

3.  

Where you see new rows in the source file, copy and paste them into the destination file.

4.  

Where you see differences between rows that exist in both files, copy the source row and paste over the destination row.

Doubtless, some of you are thinking, “Andy, you nincompoop, no one cut and pasted production data—ever.” Oh really? I’d wager that in some department in every Global 1000 company, someone somewhere regularly cuts and pastes data into an Excel spreadsheet—even now. Read on to discover two ways to perform incremental loads in SQL Server the right way—using T-SQL and SQL Server Integration Services.

Some definitions

What, exactly, is an incremental load? An incremental load is a process where new and updated data from some source is loaded into a destination, whereas matching data is ignored. That last part about matching data is the key. If the data is identical in both source and destination, the best thing we can do is leave it be.

A T-SQL incremental load

Incremental loads in SSIS

Summary

About the author