AdeMHwgM

1DROP TABLE IF EXISTS #tempProductToStockInfo;
2CREATE TABLE #tempProductToStockInfo
3(
4  [Product_Id]  INT  NOT NULL,
5  [StockInfo_Id]  INT  NOT NULL
6);
7INSERT INTO #tempProductToStockInfo
8  SELECT DISTINCT
9    productsWithSynonymsAndSplittedCodes.[Id] AS [Product_Id], 
10    stockInfo.[Id] AS [StockInfo_Id]
11  FROM [dbo.Sales].[StockInfo] AS stockInfo
12  INNER JOIN 
13  (
14    SELECT
15      productsWithSynonyms.[Id],
16      codeCross.[Code] AS [VendorCode],
17      productsWithSynonyms.[Manufacturer] AS [Manufacturer]
18    FROM 
19    (
20      SELECT  
21        products.[Id], 
22        products.[Code], 
23        UPPER(LTRIM(RTRIM(manufacturerSynonyms.[Synonym]))) AS [Manufacturer]
24      FROM [dbo.Sales].[Products] AS products
25      CROSS APPLY 
26      (
27        SELECT DISTINCT 
28          manufacturers.[Name] AS [Name], 
29          manufacturerSynonym.[ManufacturerSynonym] AS [Synonym]
30        FROM [dbo.Sales].[Manufacturers] AS manufacturers
31        CROSS APPLY (SELECT CONVERT(NVARCHAR(128), NULLIF(UPPER(LTRIM(RTRIM(split.[value]))), '')) AS ManufacturerSynonym FROM STRING_SPLIT([Synonyms], ';') AS split) AS manufacturerSynonym
32        WHERE manufacturers.[Synonyms] IS NOT NULL
33          AND manufacturerSynonym.[ManufacturerSynonym]<> manufacturers.[Name]
34          AND manufacturerSynonym.[ManufacturerSynonym] IS NOT NULL
35          AND LEN(manufacturerSynonym.[ManufacturerSynonym]) > 0
36          AND products.[Manufacturer_Name] = manufacturers.[Name]
37      ) AS manufacturerSynonyms
38      UNION
39      SELECT  
40        products.[Id], 
41        products.[Code], 
42        products.[Manufacturer_Name] AS [Manufacturer]
43      FROM [dbo.Sales].[Products] AS products
44    ) AS productsWithSynonyms
45    CROSS APPLY (SELECT CONVERT(NVARCHAR(255), NULLIF(UPPER(LTRIM(RTRIM(splitCode.[value]))), '')) AS [Code] FROM STRING_SPLIT(productsWithSynonyms.[Code], ',') AS splitCode) AS codeCross
46    WHERE codeCross.Code IS NOT NULL
47  ) AS productsWithSynonymsAndSplittedCodes
48  ON 
49    UPPER(RTRIM(LTRIM(stockInfo.[Article]))) = productsWithSynonymsAndSplittedCodes.[VendorCode]
50    AND UPPER(RTRIM(LTRIM(stockInfo.[Maker]))) = productsWithSynonymsAndSplittedCodes.[Manufacturer];