Insert rows skip duplicates

[toka]

Hello,

I want to copy data from one table to another:

INSERT INTO Table1 (codeid) SELECT codeid FROM Table2 WHERE a=7

it works, but the problem occurs when diplicate entries occur in codeid of Table1 (because is is set to No Duplicates).

As a result no rows are inserted. It's OK, cause that's the way it should be, but I was wondering if there is a way to tell MS Access to skip insertion of values that will cause duplication. So the rows that are ok will be inserted and the ones that cause duplicates will be skipped.

I just don't want to go through the loop and insert row-by-row.

Thank you.

 

[Moniker]

Use a LEFT JOIN here. It would look like this:

INSERT INTO Table1 codeid
SELECT Table2.codeid
FROM Table2 LEFT JOIN
Table1 ON Table2.codeid = Table1.codeid
WHERE Table1.codeid Is Null;

If that doesn't make sense, what you're doing is left joining one table into another on the codeid value where that codeid value on whichever table is not the master is null.

To see what this does in a clean environment, make two new tables, one called Table10 and another called Table20. Add one field to each table called "CodeTest". In Table10, add five sequential records (record 1=1, record 2=2, etc. up to Record 5=5.) In Table20, add only four squential records (record 1=1, record 2=2, up to record 4=4.) Now you have two nearly identical recordsets, with Table20 missing one record from Table10. To add it, you need a left join query.

Make a query that looks like this:

INSERT INTO Table20 ( CodeTest )
SELECT Table10.CodeTest
FROM Table10 LEFT JOIN Table20 ON Table10.CodeTest = Table20.CodeTest
WHERE (((Table20.CodeTest) Is Null));

The above will add the one missing record in Table20 (record 5=5) from Table10. The rest will be skipped (thereby avoiding duplicates). This is because the LEFT JOIN says, "Include all the records from Table10 and ONLY those records from Table20 that aren't in Table10." You can write this in the graphical query editor in Access (the QBE) by drawing the connection between the CodeTest fields, double-clicking the connecting line between the two tables, and then selecting the second Query Type.

~Moniker

 

[toka]

thank you very much!