Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009

    Unanswered: hopefully simple sql question (foxpro)

    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
    Table B

    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


  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    Here's some generic SQL to do what you want.
    INSERT INTO table_2 (some_field)
    SELECT table_1.some_field
    FROM   table_1
      JOIN table_2
        ON table_1.some_field = table_2.some_field
    WHERE  table_2.some_field IS NULL
    Let us know how you get on!
    Home | Blog

  3. #3
    Join Date
    May 2009
    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
    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.

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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts