Insert Rows into One Table that Don't Exist, from Another View

Question

My problem is extremely similar to this: Select rows which are not present in other table

but differs in that I am retrieving data from a view, rather than another table. However, the sub-select query does not return any records and I have omitted the INSERT INTO to test the SELECT query

Here is what I have got so far:

SELECT SourceT.*, DestT.Column1, DestT.Column2
FROM
    ViewA SourceT
LEFT OUTER JOIN
    TableA DestT
ON
    SourceT.Column1 = DestT.Column1 AND
    SourceT.Column2 = DestT.Column2 AND
    SourceT.Column3 = DestT.Column3 AND

**** EVERYTHING WORKS FINE UNTIL HERE

WHERE
    DestT.Column1 = NULL AND
    DestT.Column2 = NULL AND
    DestT.Column3 = NULL

If I leave out the WHERE conditions, it returns all records from SourceT along with values of NULL for those records not found in DestT - this works fine. When I add in the WHERE clause at the bottom (even on just one condition), it returns zero values.

I can try DestT.Column1 = NULL, or DestT.Column1 <> NULL - still nothing is returned. My query correctly returns NULL values but the WHERE condition does not operate, or does not allow any data through.

Any idea what is happening here? Essentially, I'm trying to insert rows from a View into TableA where they don't already exist, based on a number of conditions.

Thank you

Anyone? I've also tried adding the condition DestT.Column1 = NULL to the LEFT JOIN with no luck :/ — SomeOne, Apr 15 '16 at 12:23
I have it working without the Inner Join and adding the following: WHERE NOT EXISTS (SELECT * FROM Table! WHERE ... conditions) But damn the query is slow @ just over 2 minutes for 10,000 records in each table? — SomeOne, Apr 15 '16 at 13:01

score 1 · Accepted Answer · answered Apr 15 '16 at 13:25

1

Change the = to IS in your comparison, NULL is handled slightly differently:

WHERE
DestT.Column1 IS NULL AND
DestT.Column2 IS NULL AND
DestT.Column3 IS NULL

If you've got a non-nullable column in your destination, you should just be able to check that that is NULL instead of checking several columns - primary key would be your best bet if the table has one.

answered Apr 15 '16 at 13:25

George Dando

444
2
11

Great stuff, my bad for not paying more attention. This also get's my query time to a very acceptable 4 seconds. Kudos :) – SomeOne Apr 15 '16 at 13:49

score 0 · Answer 2 · answered Apr 15 '16 at 13:26

0

The proper syntax is 'IS NULL', not '= NULL'.

WHERE
    DestT.Column1 IS NULL AND
    DestT.Column2 IS NULL AND
    DestT.Column3 IS NULL

answered Apr 15 '16 at 13:26

dbbri

224
1
7

Insert Rows into One Table that Don't Exist, from Another View

2 Answers2