I always seem to struggle with a NOT IN clause when there are more than one column to compare. This, of course, comes up when I am trying to get records from one set that are NOT IN another set and the comparison has to be done across multiple columns. For instance, if I want to compare table 1 (users) against table 2 (importedUsers) and I want to see if the mandatory fields are there and *NOT* work with those that fail I usually ended up doing a cursor. But - there is a much easier way to do it and it does need a cursor! Thanks to David Penton for pointing me in this direction!
DECLARE @impUsers TABLE(empID VARCHAR(50), hireDate SMALLDATETIME, userNew BIT, userChange BIT, userTerm BIT)
INSERT INTO @impEmpTbl
( [empID]
, [hireDate]
, [userNew]
, [userChange]
, [userTerm]
)
SELECT i.empID, i.hireDate, 0, 1, 0
FROM [ImportUsers] i
INNER JOIN Users u ON i.[empID] = u.[empID]
WHERE NOT EXISTS (SELECT 1
FROM [Users] u2
WHERE u2.[empID] = i.[empID]
AND u2.[lastName] = i.[lastName]
AND u2.[firstName] = i.[firstName]
AND t2.[countryInfo] = i.[countryInfo]
AND t2.[emailAddress] = i.[emailAddress]
AND t2.[region] = i.[region])
Sweet, simple and elegant. The above query will produce a new record in the temp table for each record that has one or more changes across the 6 fields I am comparing in the NOT EXISTS clause. What is happening inside the parenthesis is the elegant part. The SQL Query Engine is comparing one row from the Import table to all rows in the Users (2) table. If it finds a match then it returns 1 - which means it does exist. This negates the overall WHERE clause and does not insert it into the temp table. When doing this in a cursor it was taking 30-45 seconds. As a T-SQL query it takes less than 3 seconds. That's an order of magnitude faster!