comparing 1 to many

Question

Hello,

probably a simple question sadly i am still new to sql. I have 5 columns, a first name, last name, address, zip, state, and I want to compare this table to another table with same columns. When it matches a first name from table 1 (table 1 has around 40k first names, and 2nd table has 5k), matches a first name from table 2, then insert in another table id of person (each person has an id, also has a id of the column type, for ex, first name is 1, last name is 2 etc. etc), insert id of person, insert id of column type, thats it, wondering if below is a good example of doing that, or if there is a better way for better performance, because it takes atleast 2 hours to compare all those names etc.,

declare 
@firstname nvarchar(150), @lastname nvarchar (200),@city nvarchar (250), @state nvarchar (250), @zip nvarchar (50)

select @max = COUNT(*) from AllNames

set @counter = 1

while (@counter < @max)
BEGIN
select @firstname = firstname from Person where PersonID = @counter
select @lastname = lastname from Person where PersonID = @counter
select @city = columName from Column_Name where PersonID = @counter
select @state = columName from Column_Name where PersonID = @counter
select @zip = columName from Column_Name where PersonID = @counter

if exists(select * from Person where FirstName = @firstname)
BEGIN
   insert into Match(customerid, ofacid, columnid)
   select @customerid, ProfileID, 1
   from Person a
END

if exists(select * from Person where Lastname = @lastname)
BEGIN
   insert into Match(customerid, ofacid, columnid)
   select @customerid, ProfileID, 2
   from Person a
END
if exists(select * from Person where city = @city )
BEGIN
   insert into Match(customerid, ofacid, columnid)
   select @customerid, ProfileID, 3
   from Person a
END
if exists(select * from Person where state = @state)
BEGIN
   insert into Match(customerid, ofacid, columnid)
   select @customerid, ProfileID, 4
   from Person a
END

set @counter = @counter + 1
END

Answer 1

Hello,

I am still trying to figure this out, the good thing is... I have it working, the problem is, it duplicates the results, remember I have 5k users i want to compare to a list, however it does say, hey tom was matched with this person, tom is firstname, so in the table called match, put the profile id of tom into table match, the problem is... it shows up 5k times in the match table... though i have something that should remove the duplicates but for some reason, doesnt. can anyone please help me with this on what I am doing wrong and why its duplicating when it should only insert into the table once?

here is the query below, I only inserted the first name query as there is the same query like this but for last name address etc. but first name is below:

select @max = COUNT(*) from Customers

set @counter = 1

while (@counter < @max)
BEGIN

set @customerid = @counter

select @firstname = firstname from CurrentCustomers where customerID = @counter

select @maxlistperson = COUNT(*) from person
select @maxlistdetails = Count(*) from Column_Name

set @counterlist = 1
	
while(@counterlist < @maxlistperson)
BEGIN
					
if exists(select FirstName from Person where ProfileID = @counterlist AND FirstName = @firstname)
BEGIN
					
insert into Match(customerid, listid, columnid)

select @customerid, @counterlist, 1
from Person a
set @counterlist = @counterlist + 1

END
ELSE
BEGIN

set @counterlist = @counterlist + 1
END;
						
WITH firstnamecheck
AS
(
SELECT ROW_NUMBER()OVER(PARTITION BY customerid, listid, columnid ORDER BY customerid) 

AS customerid, listid, columnid
FROM Match
						)
DELETE FROM firstnamecheck
WHERE customerid>1 
AND listid >1 
AND columnid > 1	

END		
			
set @counter = @counter + 1
END