Append only new results to a table from a query - how?

[seagullino]

Hi,

I've been searching and trying and I'm nearing the end of my patience :\

What I would like to do is to dump the results of a query (just an ID number, in my case) into a table. If the ID number already exists in the table, I would not like it to be added.

Seems so simple, yet I'm stumped and frustrated.

Would anyone be so kind to help?

Thank you!

[ADezii]

Hi,

I've been searching and trying and I'm nearing the end of my patience :\

What I would like to do is to dump the results of a query (just an ID number, in my case) into a table. If the ID number already exists in the table, I would not like it to be added.

Seems so simple, yet I'm stumped and frustrated.

Would anyone be so kind to help?

Thank you!

It is a lot simpler than you realize. I'm assuming that the [ID Number] is Unique which usually is the norm for IDs. Simply make the [ID Number] in the Table the Primary Key for the Table, if you already have a Primary Key defined for the Table, make the [ID Number] Field Indexed/No Duplicates. When you dump (Append) the ID Numbers from the Query to the Table, if they already exist, they will not be Appended.

[seagullino]

Success!!

Thanks so much. Answer was right in front me!

[NeoPa]

That's a good solution for relatively small queries, or for where the matched (pre-existing) items are small in number (and is a very handy technique to know about), but it's more efficient to write the selection criteria into the SQL itself such that it doesn't even try to add the matching records (thereby reducing the extra overhead).

EG to add IDs from table [A] into table [B] (where ID is unique key - or otherwise) :

INSERT INTO [B] ([ID])
SELECT A.ID
FROM [A] LEFT JOIN [B]
  ON A.ID = B.ID
WHERE B.ID IS NULL