Insert new records where one doesn't already exist-CodePudding

I need some help inserting new data where it doesn't already exist.

Currently I use this query:

INSERT INTO dbo.Table (column1, column2, column3, column4)
    SELECT value1, value2, value3, value4
    FROM dbo.staginTable
    LEFT JOIN dbo.Table AS A ON A.column1 = value1 
                             AND A.column2 = value2 
                             AND A.column3 = value3 
                             AND A.column4 = value4 
    WHERE A.column1 IS NULL 
      AND A.column2 IS NULL 
      AND A.column3 IS NULL 
      AND A.column4 IS NULL

This query works fine and doesn't insert data which already exists in the dbo.Table if none of the variables (value1, value2, value3, value4) is null.

My problem is that it`s very common that e.g. either value3 or value4 or both can be null and in this case the values will be inserted

Works:

(value1, value2, value3, value4)
('one', 'two', 'three', 'four')

Doesn't work:

(value1, value2, value3, value4)
('one', 'two', NULL, 'four')

I know that I could add a Unique index on the 4 columns to bypass this problem or I could use another query like insert expect but I would like to know if it's possible to use the query above or what I need to change in the query.

Thanks for your help

CodePudding user response：

I recommend using NOT EXISTS- because that makes logical sense. Then you need to combine your NULL compares with value compares as follows:

INSERT INTO dbo.MyTable (column1, column2, column3, column4)
    SELECT value1, value2, value3, value4
    FROM dbo.StagingTable ST
    WHERE NOT EXISTS (
      SELECT 1
      FROM dbo.MyTable MT
      WHERE (ST.value1 = MT.value1 OR (ST.value1 IS NULL AND MT.value1 IS NULL))
      AND (ST.value2 = MT.value2 OR (ST.value2 IS NULL AND MT.value2 IS NULL))
      AND (ST.value3 = MT.value3 OR (ST.value3 IS NULL AND MT.value3 IS NULL))
      AND (ST.value4 = MT.value4 OR (ST.value4 IS NULL AND MT.value4 IS NULL))
    );

CodePudding user response：

Given the fact that the columns you are comparing are all nullable, EXCEPT can be a more compact form

INSERT INTO dbo.MyTable (column1, column2, column3, column4)
SELECT ST.value1, ST.value2, ST.value3, ST.value4
    FROM dbo.StagingTable ST
EXCEPT
SELECT MT.value1, MT.value2, MT.value3, MT.value4
    FROM dbo.MyTable MT;

Alternatively, you can do it in a correlated subquery, this is especially useful if other columns are involved that are not being compared

INSERT INTO dbo.MyTable (column1, column2, column3, column4, column5)
    SELECT ST.value1, ST.value2, ST.value3, ST.value4, ST.value5
    FROM dbo.StagingTable ST
    WHERE NOT EXISTS (
      SELECT ST.value1, ST.value2, ST.value3, ST.value4
      INTERSECT
      SELECT MT.value1, MT.value2, MT.value3, MT.value4
      FROM dbo.MyTable MT
    );

Fear not: this subquery is completely elided by the compiler