Append Table - add only new rows (no duplicates)

[phinix]

I must be tired or something, cause I cannot do something that is pretty simple

Two tables, table1 and table2.
Table2 has same rows as table1 + some new ones.
I want to append table1 with only new rows based on primary key field - ID.

So this query will add all rows from table2 to table1, right?

INSERT INTO table1
SELECT DISTINCT table2.*
FROM table2;

Now, how can I add all new rows that are not already in table1?

This apparently doesn't work

INSERT INTO table1
SELECT DISTINCT table2.*
FROM table2
WHERE table1.ID<>table2.ID;


Please help, cause I'm loosing reality here

 

[MStef]

Nothing easier.
Look at "DemoAddNewRowsA2002" (attachment, zip).
Look at tables.
Open form and try. Look at VBA.

 

[phinix]

I think I just got it, left join and all ID that are not there

INSERT INTO table1
SELECT * FROM table2 LEFT JOIN table1 ON table2.ID = table1.ID
WHERE table1.ID Is Null;

 

[phinix]

Well, almost - I'm getting error - "Duplicate output destination ID"

Why is that? those rows, those ID fields actually don't exist in table1 so should be ok to add right?

 

[phinix]

Nothing easier.
Look at "DemoAddNewRowsA2002" (attachment, zip).
Look at tables.
Open form and try. Look at VBA.

That adds rows from one table to other deleted previous ones.
VBA just says copy object.

I would like to copy new rows from table 2 to table1 by adding only new ones to it.

 

[vbaInet]

Why do you have a DISTINCT clause in your first post?

Shouldn't it be something like this:

INSERT INTO table1 
SELECT DISTINCT table2.*
FROM table2
WHERE NOT Exists (SELECT * FROM tb1 FROM table1 AS tb1 WHERE tb1.ID = table2.ID);

OR:

1. Use the Unmatched query to find those records that don't exist in Table1
2. Using the query from step1 you INSERT into.

 

[phinix]

Why do you have a DISTINCT clause in your first post?

Shouldn't it be something like this:

INSERT INTO table1 
SELECT DISTINCT table2.*
FROM table2
WHERE NOT Exists (SELECT * FROM tb1 FROM table1 AS tb1 WHERE tb1.ID = table2.ID);

OR:

1. Use the Unmatched query to find those records that don't exist in Table1
2. Using the query from step1 you INSERT into.

I think I'm gonna have to start paying you
That worked fine, as ussusal!
I have NO IDEA why when I used "no exists" previously it didn't work.
As I said, I was loosing reality here, tried all I knew, including "no exists", nothing worked. I rewrote it and it worked, but only after you suggested it
Again, fantastic, thank you for help my friend.

 

[vbaInet]

I think I'm gonna have to start paying you

Great!!! We accept cheques, banker's draft, standing order, cash and even Pay Pal

Glad we could help