I want to link two databases that do not share a key. To do that, the closest possible match is that the manufacturer & part number fields from one table are likely to appear in the title of the other table.
It works, but the problem is that table has about 65K rows and the other has 2M. Even fractions of this search take 10-20 minutes. Is there a more efficient way to do the same thing?
SELECT BM.[ID] ,BM.[BHID] ,BM.[PartNoManufacturer] FROM [MyDB].[dbo].[BH_Master] BM INNER JOIN [OtherDB].[dbo].[product] P ON p.ProductName LIKE '%' + BM.PartNoManufacturer + '%' WHERE p.ProductName LIKE '%' + BM.Manufacturer + '%'