INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Coding and Syntax

How to merge new table into existing table by myearwood
Posted: 24 Jul 07 (Edited 25 Jul 07)

Over the years, I've seen people ask how to merge a new table into an existing table. This can be for purposes of a data conversion or a business process to synchronize two systems.

This usually involves a long process to scan the new table, seek in the existing table and either add or replace records.

I once had to merge 100,000 records into 1 million. The original code took several hours. I managed to get it down to about 15 minutes. I've refined that technique to just two commands.

CODE

UPDATE t ;
    SET ;
        t.field1 = s.field1, ;
        t.field2 = s.field2, ;
        t.field3 = s.field3 ;
    FROM ;
        target t ;
        inner join source s ;
                on t.pk = s.pk ;
    WHERE ;
        t.field1 # s.field1 ;
        OR t.field2 # s.field2 ;
        OR t.field3 # s.field3

INSERT INTO ;
    target ;
    (;
        field1,;
        field2,;
        field3) ;
    SELECT ;
        field1,;
        field2,;
        field3 ;
    FROM ;
        source s ;
    WHERE ;
        s.pk NOT IN ;
        (SELECT pk FROM target)

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close