Compating data in two different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Compating data in two different tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-14 : 07:42:26

Vivek writes "Hi DBA's,

I need to figure out how to compare the data columns in two 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 is 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.

"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-14 : 08:15:29

[code]

declare @table1 table (id int,name varchar(10))
declare @table2 table (name varchar(10))
declare @table3 table (name varchar(10))

insert into @table1
select 1,'A' union
select 2, 'C,D' union
select 3,'F'

insert into @table2
select 'E' union
select 'F' union
select 'D'

insert into @table3
select name from @table2 b
join
(
select case when charindex(',',name) = 0 then name else left(name,(charindex(',',name)-1))end as copyname from @table1
union
select substring(name,(charindex(',',name)+1),len(name)) from @table1
) a

on b.name = a.copyname

--Inserted Records
select * from @table3
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources