Skip to Main Content

Merge and Insert 50 million Records into Dimension Table

Nemesis_777Apr 17 2014 — edited Apr 20 2014

Hi My Data warehouse system upload 50 milion Dimensional records every week and load intoa STG table in  database.Every week this data comes as a full dump with additional Dimension records.

2. The STG data is moved to a Dimensional Table by creating a Surrogate Key for each record.

Every week the STG Table is truncated and freshly loaded.

Every week the  Dimensional Table hence  gets some addtional  new values .

Since the Surrogate Keys created into Dim Table  previous week  needs to be maintained how can we insert the new rows from STG to Dim with high Performance

For this i plan to create a Merge statement on DIM Table using STG table. If not matched then INSERT into Dimension Table.

Could you Suggest the best approach in terms of performance?

Post Details
Added on Apr 17 2014