Brandon Carl Goodman (4/23/2010)
I agree with ashokdasari, the MERGE is what I would do in this particular situation. I actually use the MERGE to handle TYPE II SCD in my data warehouse scenario. Very quick and powerful.
Unfortunately not everyone has SQL Server 2008 or later 🙁
Those people, including me, are stuck with creating the SCD ourselfves.
@ lbouaziz: you can achieve your proposed solution more easily with the SCD wizard. (Even better, use the SCD Kimball wizard from codeplex.) The only problem with your solution is that every update row gets issued against the database in a seperate query. Not really smart performance wise. What if you have to update one million records? One million different transactions against your database?
(and for the critics: yeah yeah, one million updates against a dimension is very unlikely. But what if it is an initial load and for some reason you have to run the package again? All updates...)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP