compare records from two tables to see if ther y match...

[prover]

I have an address table that periodically needs to be updated with new addresses from a table that is given to me once a month.

What I need is a way to compare the addresses in the new table to what is existing in the "Master" table. If there is no match then flag that new record to be appended to the master table.

My first problem is that the new addresses are sent to me with all the addresses listed (current and new). My second problem is that the file comes from an OLD (30 years) system and there is no single primary key. What makes the record considered unique is the street number, street name and ward.

The address list isn't very big, just 100,000 addresses, so what i'm looking to do is compare the two tables using a stored procedure and thats where I need help!

Has anyone done anything similar?

TIA

 

[ClaireHCM]

Hi....I didnt test it.But it may be useful for you.
I assume you name the old table as master.The new table as newtable.


create proc proc1 as

create table temp([street number] int,[street name] varchar(100),ward varchar(30),id int identity(1,1))
insert into temp select [street number],[street name],ward from newtable

declare @count int
declare @row int
declare @add varchar(500)
set @count = 1
while @count<= (select count(*) from temp)
begin
set @add = (select [street number]+&quot; &quot;+[strret name]+&quot; &quot;+ward from temp where id = @count)
set @row = (select count (*) from master where [street number]+&quot; &quot;+[strret name]+&quot; &quot;+ward = @add)
if @row = 0
begin
insert into master select [street number],[strret name],ward from temp where id = @count
end
set @count = @count+1
end
----------
--exec proc1

 

[tlbroadbent]

You definitely want to avoid a looping process because it is inefficient. Looping usually isn't necessary when dealing with sets of data in SQL. Here are a couple of SQL solutions you can modify to meet your needs. You didn't provide complete table schemas so I'm guessing a bit here.

--Use a LEFT JOIN
Insert MasterAddressTable
([street number], [street name], [ward],
..., <other columns>)
Select
n.[street number], n.[street name], n.[ward],
..., <other columns>
From NewAddressTable n
Left Join MasterAddressTable m
On n.[street number] = m.[street number]
And n.[street name] = m.[street name]
And n.[ward] = m.[ward]
Where m.[street number] Is Null

--Use WHERE NOT EXISTS
Insert MasterAddressTable
([street number], [street name], [ward],
..., <other columns>)
Select
[street number], [street name], [ward],
..., <other columns>
From NewAddressTable n
Where Not Exists
(Select * From MasterAddressTable
Where [street number] = n.[street number]
And [street name] = n.[street name]
And [ward] = n.[ward])

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm