Koen Verbeeck (8/20/2014)
Do you have any control on the process that inserts the data into the staging table?Anyway, 4 million rows is not that much for SQL Server. A few decent written TSQL statement can take care of your problem in a few minutes.
Koen, I have full control of the process. I am the one designing the SSIS package to load the data into the staging table.
The problem I have is that I need to implement a type 2 SCD. It's a business requirement to keep a history of employee records.
The other problem I am running into is the hardware configuration. I am not normally one who blames the hardware - very easy to do - but I think this time the limitations on the hardware are making it a bit difficult for me to run this process in a quick and efficient way.
Just the latest I have done is create a very simple DFT where I have the OLE DB source (a SQL command) and then SCD. It's been running for 12 hours now to load those 4 million records. This is unacceptable to me. The next stage of my project is to load another table with 10 million records ... I don't want to even imagine how long that will take if I don't do it more efficiently.