CODEWITHSUNDEEP
sql-server
daShier
daShier

Reputation: 2116

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

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).

Upvotes: 0

Views: 992

Answers (2)

san
san

Reputation: 1515

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.

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 0

Related Questions