[SOLVED] Merge inserted values against temp table leaving out null values

Merge inserted values against temp table leaving out null values

In my process I need to create an INSTEAD OF INSERT trigger which will accept the new values for the record and create a new version of it, example:

Note: Table columns are constantly changing due to business requirements. So if there is a solution that would support table to table merge instead of column to column that would be awesome.

ExampleTable

VersionID   |ID     |Value 1    |Value 2
1           |1      |abc        | 123

Example query

INSERT INTO ExampleTable (ID,[Value 1]) VALUES (1,'testabc')

Resulting table:

VersionID   |ID     |Value 1    |Value 2
1           |1      |abc        | 123
2           |1      |testabc    | 123

At this moment I have something like this:

 -- Get data
 SELECT TOP 1 * INTO #ExistingData FROM dbo.ExampleTableLatestVersionView
 WHERE  ID = @ID

 -- Merge incoming data
 MERGE #ExistingData AS target
 USING inserted as source
 ON (target.ID= source.ID)
 WHEN   MATCHED
    THEN UPDATE SET target.[Value 1] = source.[Value 1], 
                    target.[Value 2] = source.[Value 2];

-- And afterwards I do a new insert into version table

Problem here is that NULL values from inserted table are overwriting and I end up with this:

VersionID   |ID     |Value 1    |Value 2
1           |1      |abc        | 123
2           |1      |testabc    | NULL

I was thinking of doing INSTEAD OF UPDATE where I could get previous values by referencing VersionID, but I want to know if this is possible.

Solution

This will use the existing value if provided value is null:

 MERGE #ExistingData AS target
 USING inserted as source
 ON (target.ID= source.ID)
 WHEN   MATCHED
    THEN UPDATE SET target.[Value 1] = ISNULL( source.[Value 1],target.[Value 1]), 
                    target.[Value 2] =  ISNULL( source.[Value 2],target.[Value 2]);