Sql-server – Insert up to 10 missing products into a related product discounts table

sql servert-sql

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?

Best Answer

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).

Related Question