Append query how to add only new rows?

[siamandm]

Hello All,
if I have two tables, both have the same column name, and one of them contains new rows, how to add those rows using append query without duplicate rows?

Regards

[HansV]

How can we detect duplicate rows? Is there a unique ID that the two tables could have in common, or do we have to look at the combination of a few fields, or at all fields?

[siamandm]

Thank you for the reply,
to make it simple and for learning purposes, lets say we have a unique column ID as the primary key, both tables have the same structure.

Regards

[HansV]

You could use SQL like this:

INSERT INTO Table1 SELECT Table2.* FROM Table2 WHERE Table2.ID Not In (SELECT Table1.ID FROM Table1)

[siamandm]

Thank you very much it worked very well, now if in case we want to make it more complex and we want a combination of more than one column to make primary key what we do please?

[HansV]

For example:

INSERT INTO Table1
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON Table2.Field1 = Table1.Field1 AND Table2.Field2 = Table1.Field2
WHERE Table1.Field1 Is Null

You can add more fields to the JOIN clause if required.

[siamandm]

Thank you very much for the reply, I Have tried this method below but it shows that inserting 0 row!
why is that happening, please?

append query.png

[HansV]

Change the WHERE clause to

WHERE employee_old_data.[Full Name] Is Null

[siamandm]

thank you for the reply, I have tried this as well still same!

[HansV]

Could you attach a (zipped) sample database?

[siamandm]

I just checked the old table and found the rows have been added despite the message I got appending 0 rows. why is that?

[siamandm]

I did another test, i deleted some rows from the beginning and run the query again it didn't work, here is the file

Database3.zip

[HansV]

The query in your sample database is different from the one in your screenshot: the WHERE clause in the database is

WHERE Employee_Old_Data.eeid and employee_old_data.[full name] is null;

The part Employee_Old_Data.eeid and does not belong there, it should be

WHERE employee_old_data.[full name] is null;

corresponding to my suggestion. If you then run the query, you get

S1961.png

[siamandm]

Yes, you are right I have played with it a bit, is there any reason you used the full name in the where clause?

[HansV]

You have to use one of the fields that is guaranteed not to be empty; the safest choices are the fields in the JOIN clause. I arbitrarily chose full name, but you could use EEID just as well.

[siamandm]

thank you very much for the well explained answer.

[siamandm]

If I accidentally enter the auto new number, i.e. missed row at "11".
How can I insert a row called "11" back to AutoNumber cell?

[HansV]

You should not do that, and there is no need to worry about it. An AutoNumber field is a unique identifier that has no intrinsic meaning. It does not matter if there are gaps in the AutoNumber sequence.

[siamandm]

Thank you very much for the clarification