SQL - Insert into table if not exists - with existing NULL values

Question

I need to import data into SQL from Excel via a .NET user application. I need to avoid duplication. However, some records might have NULLs in certain columns.

I'm using stored procedures to implement the imports, but I can't seem to provide a "universal" solution that checks for matching data if it exists or NULLS if the data doesn't exit.

Note that my Part table uses an Identity PK, but the import records won't include it.

Below is an example (I did not include all the columns for brevity):

CREATE PROCEDURE [dbo].[spInsertPart] 
    (@PartNo NCHAR(50), 
     @PartName NCHAR(50) = NULL,
     @PartVariance NCHAR(30) = NULL)
AS
BEGIN
    SET NOCOUNT OFF;

    IF NOT EXISTS (SELECT PartNo, PartVariance 
                   FROM Part 
                   WHERE PartNo = @PartNo AND PartVariance = @PartVariance)
    BEGIN
        INSERT INTO Part (PartNo, PartName, PartVariance)
        VALUES (@PartNo, @PartName, @PartVariance 
    END
END

The import data may or may not include a PartVariance, and the existing records may (or may not) also have NULL as the PartVariance.

If both are NULL, then I get a duplicate record - which I don't want.

How can I re-write the procedure to not duplicate, but to treat the NULL value like any other value? (That is, add a record if either contains NULL, but not both).

san · Accepted Answer · 2019-06-16 07:08:29Z

1

I think you need to provide clear information on the following before this questions can be correctly answered: What are the columns based on which 'matching' of an incoming record is performed against the rows of the 'Part' table? What that means is having same values on which columns would require the rest of the columns of 'Part' table to be rather 'updated' with incoming values VS a new record would be 'inserted' into the 'Part' table.

Considering only 'PartNo' and 'PartVariance' columns to be used for 'matching' as seen in the query and only PartVariance column can have NULL, here would be the solution:

CREATE PROCEDURE [dbo].[spInsertPart] 
        (@PartNo NCHAR(50), 
         @PartName NCHAR(50) = NULL,
         @PartVariance NCHAR(30) = NULL)
    AS
    BEGIN
        SET NOCOUNT OFF;

        IF NOT EXISTS (
                        SELECT 1 
                        FROM   Part 
                        WHERE  PartNo = @PartNo 
                        AND    COALESCE(PartVariance, '') = COALESCE(@PartVariance, '')
                      )
        BEGIN
            INSERT INTO Part (PartNo, PartName, PartVariance)
            VALUES (@PartNo, @PartName, @PartVariance)
        END
    END

Note:- You have mentioned that only PartVarince can be NULL. If same is true with PartNo, then COALESCE can be used for matching PartNo column as well.

answered Jun 16, 2019 at 7:08

san

1,52512 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

daShier Over a year ago

Thank you san! COALESCE (returns the first non NULL) did the trick. And, no, in this case, the PartNo can't be NULL, so your script as written worked as needed.

Zohar Peled Over a year ago

Note: This is a valid solution as long is an empty string is not considered as a valid value for the PartVariance column.

san Over a year ago

@ZoharPeled I agree. If they DO need to store blank ('') and NULL as 2 separate values (that sounds like a bad business decisions though), then the matching conditions can be slightly altered to accommodate the same.

Zohar Peled Over a year ago

@san empty strings are different than null. Any use of coalesce / isnull for null / value comparison will have to use some magic value (what if it was numbers? would 0 or -1 be a valid choice?) Therefor, IMHO, this can only be considered a valid solution under some circumstances. And who's to say that the circumstances will not change over time? That's why I prefer to write a little more code to avoid the use of magic values for such tasks.

Zohar Peled · Accepted Answer · 2019-06-16 07:07:58Z

0

Well, NULL is a problem when it comes to SQL Server. You can't use equality checks on it (=, <>) since they both will return unknown which will be translated as false.

However, you can use a combination of is null, or and and to get the desired results.

With sql server 2012 or higher (in older versions change iif to a case), you can do this:

IF NOT EXISTS (SELECT PartNo, PartVariance 
               FROM Part 
               WHERE IIF((PartNo IS NULL AND @PartNo IS NULL) OR (PartNo = @PartNo), 0, 1) = 1
               AND IIF((PartVariance IS NULL AND @PartVariance IS NULL) OR (PartVariance = @PartVariance), 0, 1) = 1)

If both PartNo and @PartNo are null, or they contain the same value (remember, null = any other value will be evaluated as false) - the IIF will return 0, otherwise, (meaning, The column and the variable contains different values, even if one of them is null), it will return 1.

Of course, the second iif does the same thing for the other column/variable combination.

answered Jun 16, 2019 at 7:07

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

2 Comments

daShier Over a year ago

Thank you for your suggestion, but this didn't work when I tested it. When no record exists with the PartNo being added, then the new record didn't get inserted into the table. The COALESCE function did do what I was looking for.

Zohar Peled Over a year ago

I think that I got the 0 and 1 mixed up. However, the coalesce option is easier to write, read and maintain, so if an empty string is not a valid value for PartVariance then you should go with that solution.

Collectives™ on Stack Overflow

SQL - Insert into table if not exists - with existing NULL values

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related