Hi Guys first post so excuss the stupidity of the question.

I have two tables and they have the same fields.
One table has 500,000+ records and the other has 1000+ records.

What I do at the moment is do a query to see if the data from table 2 record 1 exists in table 1. If it does not then I insert the record into table 1. If it does already exist then I move on to the next one. Repeart for record 2 etc...

This is all done in basic using a foxpro ODBC driver.

Works well as long as Table 1 is under 100,000 records, if it is more then the select can take upto 20 seconds for the one query!

Is there a single SQL query which can merge the two tables or produce a select which I can then delete table A and re-populate.

By Merge I mean that if the record appears in both table A and B it is ignored else it is recorded. Some thing like this

Table A
1,2,3,6,7
Table B
1,2,4,5,9

And the result is 1,2,3,4,5,6,7,9

NOT 1,1,2,2,3,4,5,6,7,9

To add extra deficulty there are 101 fields per record and there is the possiblity if 99 being the same but 1 or 2 being different. So I cannot use a single field to check or use a "where" as the 1 or 2 differences could be any fields.

It may not be possible but the other option is to break table A into single days thus creating tableA-01 tableA-02 ... tableA30 tableA31 which would give me smaller databases which would search quicker.

PSS the insert seems to be fine it is just the search which takes for everrrr.

Many thanks

Damon

Here's some generic SQL to do what you want.
Let us know how you get on!

Yes, as you said the search takes time (obviously you cannot easily index on all the 101 fields).

So I suggest:

Use table1
Copy to table1.txt type sdf (to retain the exact column spacing and thus enabling proper sorting).
Use table 2
Copy to table2.txt type sdf.

Now go to the OS prompt. Sort-merge the two files using unique to eliminate duplicates (needs little work).

In FoxPro,
Use table1
Zap
Append from sorted.txt type sdf

Obviously, if you have to do it regularly, the method has to be different. In that case, I would write a small prg that uses the old "b-line" or balanced-line method taught in COBOL Essentially, it consists of:

-> Having two sorted files.

EOF1=0
EOF2=0
Do While not (EOF1 =1 and EOF2 = 1)

Loop
OK
End