SQLTeam.com | Weblogs | Forums

Appending Unique Records into a Table


#1

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.


#2

'Record is deleted' doesn't seem to be a sql server error.

You have 2 conditions in your where clause. Try running the query with one condition at a time to see which condition is not returning the expected results.


#3

No, it isn't SQL server error, it is Access.

Both conditions seems to work fine, see the SELECT query.


#4

First condition doesn't work in the append query, but if I make select query (second) out of it, it works perfectly well. Any ideas why this is happening?


#5

So the only difference between your Insert query and the Select statement is the LEFT JOIN. Take another look at that and the data in that table.


#6

It pulls site name from another table based on its ID. Why would this be a problem?


#7

Any ideas, please?