I found the article below describing this same issue with the Oracle merge statement. It also described a work-around:
I have been unable to find a solution to this issue for SQL Server 2008. I am trying to do basic ETL from Staging table to a Type II dimension table in a Data Warehouse. I am using the Merge statement with the Unique key minus the modification
date as my merge key list. I have a handful of columns (in both Staging and the DW) containing nulls in my merge key list. I have tried the ISNULL function and the ANSI_NULL db option (OFF) with no success. When I put zeros in the null
columns, it works as expected. I am about to try separate INSERT and UPDATE statements, but I fear that the NULL comparison issue will bite me there with the JOIN statement. Has anyone else experienced this? NULL values are valid in our Data
Warehouse. How can I make T-SQL handle them?
Thank you for your help, Jesse
View Complete Post