Question

  • Creator
    Topic
  • #3937962

    software coding

    by antonymartinkollam ·

    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?

You are posting a reply to: software coding

The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our Community FAQs for details. All submitted content is subject to our Terms of Use.

All Answers

  • Author
    Replies
    • #3939326
      Avatar photo

      Re: copy without duplicates

      by kees_b ·

      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.

Viewing 0 reply threads