SQL Server 2008 onwards has merge statement that allow us to more easily perform common data warehouse tasks. Below is link to Kimball Group Tip 107 that gives an excellent example of using merge with SCD type 1 and type 2.
Using the SQL MERGE Statement for Slowly Changing Dimension Processing
NOTE: Thanks Phil for pointing out that previous link stopped working. Kimball Group website was re-designed and the old linked stopped working (it is going to hurt many website and probably Kimball Website most for not getting usual traffic)