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

Col

Name

E
F
D

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.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 data..like

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...sqlsql

Compare records between two tables

Hi all,
I have table A and populate table B with table A but before I do that I
need to compare records in between those two tables...
If there are new records on table A, I need to insert it into
newTable and if there are records that has changed then I need to insert
into updateTable.
How do i compare two tables row by row if price or description field
has changed.?
Thanks
See if the examples from this page help:
SQL Server DTS best practices
http://vyaskn.tripod.com/sql_server_..._practices.htm
This is a longish article, but to jump straight to the topic, search for the
keyword DDQ on this page
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Kiran B." <kbyajankar@.yahoo.com> wrote in message
news:uCyilibmEHA.3428@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have table A and populate table B with table A but before I do that
I
> need to compare records in between those two tables...
> If there are new records on table A, I need to insert it into
> newTable and if there are records that has changed then I need to insert
> into updateTable.
> How do i compare two tables row by row if price or description
field
> has changed.?
> Thanks
>
>

combo box error

I have a combo box that displays a list of records.
When a user clicks on a combo box selection all the textboxes will populate with a value from the selected record of the combo box. But instead of populating the fields it gave me an error message: Object doesnt support this property or method

I am developing in Access 2000 and using SQL Server as back-end.

Sub Combo5_AfterUpdate()
' Error occurred next line
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo5]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Thank you!This ain't be SQL problem could be Access, check with Access forums.