Append query how to add only new rows?

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Append query how to add only new rows?

Post by 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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append query how to add only new rows?

Post by 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?
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: Append query how to add only new rows?

Post by 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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append query how to add only new rows?

Post by HansV »

You could use SQL like this:

INSERT INTO Table1 SELECT Table2.* FROM Table2 WHERE Table2.ID Not In (SELECT Table1.ID FROM Table1)
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: Append query how to add only new rows?

Post by 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?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append query how to add only new rows?

Post by 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.
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: Append query how to add only new rows?

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append query how to add only new rows?

Post by HansV »

Change the WHERE clause to

WHERE employee_old_data.[Full Name] Is Null
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: Append query how to add only new rows?

Post by siamandm »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append query how to add only new rows?

Post by HansV »

Could you attach a (zipped) sample database?
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: Append query how to add only new rows?

Post by 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
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: Append query how to add only new rows?

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append query how to add only new rows?

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: Append query how to add only new rows?

Post by 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?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append query how to add only new rows?

Post by 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.
Best wishes,
Hans

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: Append query how to add only new rows?

Post by siamandm »

thank you very much for the well explained answer.