Insert up to 10 missing products into a related product discounts table

I have two tables: products and products_discounts. If a product is designated as a “Top 10” product, and it is missing a defined discount in products_discounts, I want to insert a record into products_discounts for each missing record. The inserted values would consist of the product_id and two other hard-coded values in the INSERT INTO.

Products

id, product_id, name, top_ten

Products_Discounts

id, product_id, discount_amount, discount_description

The following is how I plan to do this manually, but since I’ll be repeating this process a few times a year, I’m looking to see if this can be done in a few lines of SQL.

(1) Get the list of products that meet my criteria.

SELECT p.product_id, pd.discount_amount FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL

(2) Use a text editor to construct each individual INSERT INTO statement. I’d use the output of step 1 to get the product_ids but the other two values would remain the same (hard-coded).

INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES ('<product_id goes here>', 10, 'Top 10 Product')

Can these steps be combined into a few lines of SQL?

I’m having a hard time understanding why you can’t just do this:

INSERT dbo.products_discounts (product_id, discount_amount, discount_description)
SELECT p.product_id, 10, 'Top 10 Product'
FROM dbo.products AS p
LEFT OUTER JOIN dbo.products_discounts AS pd
     ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
  AND pd.product_id IS NULL;

Actually, more intuitively, IMHO:

INSERT dbo.products_discounts (product_id, discount_amount, discount_description)
SELECT p.product_id, 10, 'Top 10 Product'
FROM dbo.products AS p
WHERE p.top_ten = 1 
AND NOT EXISTS
(
   SELECT 1 FROM dbo.products_discounts AS pd
     WHERE p.product_id = pd.product_Id
);

Is it because the discount amount (10) changes depending on the product? Share with us what rules you use to determine if the discount should be something other than 10. If you can’t define those, neither can SQL Server (nor can dynamic SQL, or loops, or cursors, etc).

This might do what you want:

SELECT 'INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES (''' + CONVERT(varchar(30), p.product_id) + ''', 10, ''Top 10 Product'')
'
FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL;

It generates statements for all top-10 products that don’t have a product discount. No need to do this in Excel. Just copy-and-paste the results into a new query window in SSMS and run it. Having said that, you’ll likely want to automate this procedure, which could be accomplished using either a cursor like this:

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT 'INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES (''' + CONVERT(varchar(30), p.product_id) + ''', 10, ''Top 10 Product'')
'
FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL;

OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sys.sp_executesql @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

Or in a set-based approach, like this:

DECLARE @cmd nvarchar(max);
SELECT @cmd = CASE WHEN @cmd IS NULL THEN N'' ELSE @cmd END 
    + N'INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES (''' + CONVERT(varchar(30), p.product_id) + ''', 10, ''Top 10 Product'')
'
FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL;

EXEC sys.sp_executesql @cmd;

What about this as a solution? I apologize if I missed something in your question about needing to set and change the value of the discount by product, but this will add all missing product id’s to the list with a default discount.

INSERT INTO products_discounts
(product_id, discount_amount, discount_description)
SELECT P.product_id
    , 10
    , 'Top 10 Discount'
FROM products AS P
    LEFT OUTER JOIN product_discounts as pd ON pd.product_id = P.product_id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL