View Single Post
  #1 (permalink)  
Old March 21st, 2007, 06:01 AM
pallone pallone is offline
Friend of Wrox
Points: 1,243, Level: 13
Points: 1,243, Level: 13 Points: 1,243, Level: 13 Points: 1,243, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Oct 2003
Location: , , United Kingdom.
Posts: 290
Thanks: 24
Thanked 0 Times in 0 Posts
Default Compare two fields from different tables


I would appreciate very much if someone could help me to figure out how to do the following using SQL:

I have to compare the values of two fields called Name(nvarchar) in two different tables and then if they match (or contain something similar) I have to get the CompanyID of the Company table and check if this is already present in the CompanyCompare table. If it is not then I will insert the whole record in the CompanyCompare table.

Using a cursor I was able to do the insertion based on the CompnayID. However, I cannot figure out how to compare the two string fields for a match or similarities in the loop.

I was reading about the CONTAINS keyword but am not sure this would work here since I would need to use a variable in the loop and I think it only accepts a string as parameter.

Also, I would appreciate if someone could show me some sample code of how to do this. Please, feel free to change my code below.


declare @p_TableID as int
declare @match as int

Begin Transaction
   declare tb_cur cursor
   for Select CompanyID from Company
     open tb_cur
        fetch next from tb_cur into @p_TableID
        while @@fetch_status = 0
           set @match = isNull((Select CompanyID from CompanyCompare where CompanyID = @p_TableID),-1)
           if @match = -1
              Insert into CompanyCompare (CompanyID, Name, MaxValue)
              Select CompanyID, Name, MaxValue from Company where CompanyID = @p_TableID
           fetch next from tb_cur into @p_TableID
     close tb_cur
     deallocate tb_cur
Commit Transaction
Reply With Quote