Software·43,935 discussions

software coding

i have two ms access table ( TableA & TableB) .. how i can copy from TableB to TableA..Avoid duplicate records
My code below
Insert into TableA (slno,name) select slno,name from TableB

duplicate slno copy to TableA….
How i can stop?

Topic

Re: copy without duplicates

In reply to software coding

Three options. I leave it to you to determine what’s the best for you.

1. Make slno a primary key in tableA.
2. Extend the query with a NOT IN clause to check if it’s already a record with the same slno in TableA.
3. Make query an outer join on slno and only select the records from tableB that have null in the slno-field from table A.

Notes:
– Using only option 1 triggers a warning message, but you reach your purpose.
– Option 2 or 3 is much faster if you combine it with option 1.

In fact, making slno a primary key lets Access take care of duplicates. With Option 2 and 3 you make a query giving only the records for which slno does not yet exist and then insert those.
Personally I prefer to have control myself instead of having to say “OK” to a warning about duplicate records.

[Author]

Replies