I'm trying to append LastLogin_Date table with only unique records based on LastLogin_Date field.
If I run following query, it returns 'Record is deleted'.
INSERT INTO LastLogIn_SnapShots ( Name, Surname, Forename, LastLogin_Date )
SELECT dbo_Site.Name, dbo_AppUser.Surname, dbo_AppUser.Forename, dbo_AppUser.LastLogin_Date
FROM dbo_AppUser LEFT JOIN dbo_Site ON dbo_AppUser.Employee_Site_ID = dbo_Site.ID
WHERE ((dbo_AppUser.LastLogin_Date Not In (SELECT LastLogin_Date FROM LastLogIn_SnapShots WHERE LastLogin_Date is not null)) AND (dbo_AppUser.Employment_End) Is Null);
Obviously, problem lies in WHERE clause, but if I turn it in simple SELECT query it pulls correct data.
SELECT LastLogin_Date
FROM dbo_AppUser
WHERE ((LastLogin_Date Not In (SELECT LastLogin_Date FROM LastLogIn_SnapShots WHERE LastLogin_Date is not null)) AND (dbo_AppUser.Employment_End) Is Null);
Please help to identify the problem.