Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: Comparing columns in two tables

    Hi DBA's,

    I need to figure out how to compare the column data in two distinct tables. I have two files that populate these two tables. Basically I am doing a file comparison here. Let me explain the process:

    Table 1

    Col 1 Col 2

    ID Name

    1 A
    2 C,D
    3 F

    Table 2




    Now if there is any data that is present in Table 2 that matches with the data in table 1 then I need to write the entire record of table 2 into a separate table OR file.

    Here is what I think I need to do.

    1. Take first record from Table 1 and scan Table 2 to see if the Name 'A' exists. If yes put/insert the record from Table 2 in a seprate table say table 3 and then go to the second record. If no match then go directly to the second record in table 1. Repeat the process till every record in table 1 is compared to the records in table 2.

    2. Now the trick here is some Names have only last name. Others have last name and first name. So for Table 1, Name C,D should be a match to D in Table 2. I have to send this record to Table 3. How do I accomplish that? Should I spilt the Col2 into columns. How do I do that?

    Please note that table 2 would have close to 5000 records.

    Please advise.

    Thanks in anticipation.

  2. #2
    Join Date
    Nov 2002
    Think you'll be having a lot of false matches on Smtih and Jones...

    Maybe not Kaiser though....

    Can you post the DDL of the Tables, and sample

    CREATE TABLE myTable99 (Col1 int, Col2, varchar(50), ect

    For Sample Data, something like..

    INSERT INTO myTable99 (Col1, Col2, ect)
    SELECT 1, 'Brett Kaiser', ect UNION ALL
    SELECT 2, 'Indiana Jones', ect UNION ALL
    SELECT 3, 'Jones', ect UNION ALL
    SELECT 4, 'Jeff Smith', ect UNION ALL

    get the picture?

    It's easier to help when we have the actual stuff...

    Still think the matching will be a fudge though..

    maybe you can match on exact, remove that population, then do the fudge on a smaller subset...

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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