CODEWITHSUNDEEP
sqlsql-server
Kdaimati
Kdaimati

Reputation: 1

Missing records

I copied a view to a new table. However, some records exist in the view but not in the table.

Furthermore, I can not find all missing records when I use SELECT ... NOT IN:

DECLARE @email NVARCHAR(256)='[email protected]'
SELECT Email FROM dbo.vw_DirectoryData WHERE Email=@email
SELECT Email FROM dbo.tblDirectoryData WHERE Email=@email
SELECT Email FROM dbo.vw_DirectoryData WHERE email NOT IN(SELECT email FROM dbo.tblDirectoryData)

Result:

Email
[email protected]
(1 row(s) affected)

Email
(0 row(s) affected)

email
(0 row(s) affected)

I tried another approche:

DECLARE @email NVARCHAR(256)='[email protected]'
SELECT     vw_DirectoryData.Email, tblDirectoryData.Email AS [Email in Table]
FROM         vw_DirectoryData LEFT OUTER JOIN
                      tblDirectoryData ON vw_DirectoryData.Email = tblDirectoryData.Email WHERE vw_DirectoryData.Email=@email

SELECT     vw_DirectoryData.Email, tblDirectoryData.Email AS [Email in Table]
FROM         vw_DirectoryData LEFT OUTER JOIN
                      tblDirectoryData ON vw_DirectoryData.Email = tblDirectoryData.Email WHERE tblDirectoryData.Email IS null

    Result:

    Email
Email in Table

    [email protected]
NULL

    (1 row(s) affected)

    Email                                                                                                                                                                                                                                                           Email in Table
    (0 row(s) affected)

any advice how to overcome this issue?

Upvotes: 0

Views: 88

Answers (3)

Kdaimati
Kdaimati

Reputation: 1

I figure it out, the problem was in the data type. I was reading from different tables to create the view. the view use UNION ALL to combine data from 3 other views.

I solved the problem by creating SSIS package that read the sources data then insert it in standard tables. Then read the data and apply all format and join I need in same views.

Upvotes: 0

JBrooks
JBrooks

Reputation: 10013

Your

NOT IN (SELECT email FROM dbo.tblDirectoryData)

will work if you take out all of the NULLs. So add the WHERE clause.

NOT IN (SELECT email FROM dbo.tblDirectoryData WHERE email IS NOT NULL)

Think of NULL as "Unknown", so if the email is unknown it doesn't know if it is equal to something or not (could be...)

Upvotes: 1

CoridRocket
CoridRocket

Reputation: 71

Try using NOT EXISTS:

SELECT viw.Email
FROM dbo.vw_DirectoryData viw 
WHERE NOT EXISTS(SELECT tbl.* FROM dbo.tblDirectoryData tbl WHERE tbl.Email = viw.Email)

Upvotes: 0

Related Questions