The surrograte key is an identity field, how can it have zero in it?
What I usually do is loading all the data in a staging table. Then using left join to insert the new records into the dimension table. The dimension table does not need to have the surrogate key.
INSERT INTO Dim(Natural_key, attribute1, attribute2, load_time)
SELECT s.natural_key, s.attribute1, s.attribute2, GETDATE()
FROM staging_table s
LEFT JOIN dw.dbo.dimension dim ON s.natural_key = dim.natural_key WHERE dim.natural_key is NULL
BTW, your step 2 insert statement, did you forget to insert the natural_key?
If the records are already in the dimension table, then update the record with the most recent information.
Update d set d.attitute1=s.attitute1, d.attitute2 = s.attitute2, d.load_time=GETDATE() FROM dw.dbo.dimension d inner join staging_table s where (d.natural_key = s.natural_key) and (d.attitute1 <> s.attitute1 or d.attitute2 <> s.attitute2)
I don't know what kind of benefit to do SCD3. I think the effective date and expiration date is good enough in dimension table. In SCD3 you are changing the dimension table structure. What if the attribute changes 10 times, you would have a big table.