Question :
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_tenProducts_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_id
s 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?
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).
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