Generate all attribute combinations for all stock in one job?

Do you already have the attributes setup?  There are a lot of aspects of the query specific to my application, but it can be adapted to work purely within NopCommerce.  The attribute data has to exist somewhere first though before the combination XML can be generated.  

I recommend fully setting up 1 product manually within the Admin, then selecting all of its data so you can see what it looks like.  Then you can extrapolate out to the general case.

Yes i see.
I have all products in the database with all data.
A lot of products even have the combinations, but it has been done manually product by product.
Thats the only way to be able to give a variant the proper GTIN.

It just would be easier to do this by script as we now have 4.000 products without combinations.

Assuming you have your Attributes (size, color, etc) and Attribute Values (small, medium, large || red, green, blue, etc) populated in the database, then this will generate the XML combinations.  

Note that I have not tested this on more than 1 product nor more than 1 combination, so please do your own testing before commenting out the last line (where nop.Id = [YOUR_TEST_PRODUCT_ID].

INSERT INTO ProductAttributeCombination (
  --Id,
  ProductId,
  AttributesXml,
  StockQuantity,
  AllowOutOfStockOrders,
  Sku,
  ManufacturerPartNumber,
  Gtin,
  OverriddenPrice,
  NotifyAdminForQuantityBelow,
  PictureId
)
SELECT DISTINCT
  --Id
  --ProductId
  nop.Id,
  --AttributesXml
  (SELECT map.Id AS "@ID",
  (SELECT val.Id AS "ProductAttributeValue/Value"
  FOR XML PATH (''), TYPE)     
  FOR XML PATH ('ProductAttribute'), root ('Attributes')),
  --StockQuantity
  0,
  --AllowOutOfStockOrders
  1,
  --Sku
  null,
  --ManufacturerPartNumber
  null,
  --Gtin
  '[YOUR_DATA_HERE]',
  --OverriddenPrice
  null,
  --NotifyAdminForQuantityBelow
  0,
  --PictureId
  0
FROM dbo.Product nop
INNER JOIN Product_ProductAttribute_Mapping map
  ON nop.Id = map.ProductId
INNER JOIN ProductAttribute attr
  ON attr.Id = map.ProductAttributeId
INNER JOIN ProductAttributeValue val
  ON val.ProductAttributeMappingId = map.Id
--testing
WHERE nop.Id = 40987;

Hi

well its getting close, but this will only give me one attribute per row.
I need both color and size as that is the actual variant with a specific ean number:

2+ attributes is more complex.  I have the query worked out using a separate table that houses my legacy data, but will need a day or two to update it to reference only nopCommerce tables.

Well i have been trying my self, please let me know if you succeed

So either this was more complicated than I expected, or I simply made it more complicated than it needs to be.  I'm not entirely certain if it works for more than 2 attribute combinations, nor if it works on more than just 1 test product at a time.  I'm sure there is a more elegant way to get the cartesian product without hammering it out in 2 different CTEs, but this was simply the first way that worked.  

It should get you closer though, and I would test test test, I can't emphasize enough how much I would test this before touching production data.   I didn't even look at the execution plan because there are just some things I don't need to know.  The @MAXoptionCount is intended to help prevent blowing up your database in case you happen to have a product with an unruly number of attributes.

Also, there is some question on where available stock comes from.  I know where it comes from in my source data, but I just winged it for this case.  The final SELECT is mostly redundant so you can pull in your GTIN from wherever it resides.  

DECLARE @MINoptionCount int,
    @MAXoptionCount int
SET @MINoptionCount = 1;
SET @MAXoptionCount = 3;

--get products with attributes and how many potential combinations
WITH multiOptions AS (
  SELECT  ProductAttributeMappingId,
      COUNT(DISTINCT(ProductAttributeMappingId)) AS [optionCount]
  FROM ProductAttributeValue
  WHERE [Name] IS NOT NULL
  GROUP BY ProductAttributeMappingId
  ORDER BY COUNT(DISTINCT(ProductAttributeMappingId)) DESC
  --required for CTE
  OFFSET 0 ROWS
),

--get associated mappings
optionArray1 AS
(
  SELECT val.Id AS "valId",
      map.Id AS "mapId",
      map.ProductId,
      val.AssociatedProductId,
      val.PriceAdjustment AS "Price",
      val.Quantity
  FROM ProductAttributeValue val
  INNER JOIN Product_ProductAttribute_Mapping map
  ON val.ProductAttributeMappingId = map.Id
  INNER JOIN multiOptions sub
  ON val.ProductAttributeMappingId = sub.ProductAttributeMappingId
  WHERE val.[Name] IS NOT NULL
  AND sub.optionCount BETWEEN @MINoptionCount  AND @MAXoptionCount
  --testing
  AND map.ProductId= 40987  
),

optionArray2 AS
(
  SELECT val.Id AS "valId",
      map.Id AS "mapId",
      map.ProductId,
      val.PriceAdjustment AS "Price",
      val.Quantity
  FROM ProductAttributeValue val
  INNER JOIN Product_ProductAttribute_Mapping map
  ON val.ProductAttributeMappingId = map.Id
  INNER JOIN multiOptions sub
  ON val.ProductAttributeMappingId = sub.ProductAttributeMappingId
  WHERE val.[Name] IS NOT NULL
  AND sub.optionCount BETWEEN @MINoptionCount  AND @MAXoptionCount
  --testing
  AND map.ProductId= 40987  
),

cartesianProduct AS (
  SELECT 
    CAST(opt1.valId AS VARCHAR(12)) + ',' + CAST(opt2.valId AS VARCHAR(12)) AS [XMLstring],
    opt1.mapId,
    opt1.ProductId,
    opt1.Price,
    --get minimum quantity from available options or from somewhere else?
    opt1.Quantity
    FROM optionArray1 opt1
  CROSS JOIN optionArray2 opt2
  WHERE opt1.ProductId = opt2.ProductId
    AND opt1.mapId < opt2.mapId
),

--parse CSV from cartesian product to XML
buildXML AS (
  SELECT 
    --Id
    --ProductId
    ProductId,
    --AttributesXml
    CONVERT(NVARCHAR(Max),
    (SELECT mapId AS "@ID",
      (SELECT Value
        FROM STRING_SPLIT([XMLstring], ',') AS [Value]      
      FOR XML PATH ('ProductAttributeValue'), TYPE
      ) 
    FOR XML PATH('ProductAttribute'), TYPE, ROOT('Attributes')
    )) AS [AttributesXml],  
    --StockQuantity
    Quantity,
    --OverriddenPrice
    Price    
    FROM cartesianProduct 
)

INSERT INTO ProductAttributeCombination (
  --Id,
  ProductId,
  AttributesXml,
  StockQuantity,
  AllowOutOfStockOrders,
  Sku,
  ManufacturerPartNumber,
  Gtin,
  OverriddenPrice,
  NotifyAdminForQuantityBelow,
  PictureId
)

SELECT 
  --Id
  --ProductId
  ProductId,
  --AttributesXml
  [AttributesXml],  
  --StockQuantity
  Quantity,
  --AllowOutOfStockOrders
  1,
  --Sku
  null,
  --ManufacturerPartNumber
  null,
  --Gtin
  '[YOUR_DATA_HERE]',
  --OverriddenPrice
  Price,
  --NotifyAdminForQuantityBelow
  0,
  --PictureId
  0
FROM buildXML;

Finally got some time for this.
I will test it and let you know... thanks a lot

Good luck!  

Looking at it again it is only going to work for combinations of exactly 2 attributes because of the way I hardcoded optionArray1 and optionArray2.  There is probably a better way to handle that recursively for 'n' attributes but that would require more tinkering.

Thats just fine by me, I only have Size and Color