3

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?

  • Why not create a stored proc that generates the sql dynamically and then have a job execute the proc on a set schedule? – Steve Mangiameli Feb 14 '17 at 19:30
  • Primarily because I don't actually want this to happen every time in the same way. I'm directly manipulating the DB of an ERP system we use and these tasks would normally be done through their GUI. – Chris76786777 Feb 14 '17 at 19:39
  • What is the question then? It sounds like you have it figured out. – Steve Mangiameli Feb 14 '17 at 19:40
  • Sorry. The question is, can I do this in a single statement? As if in a for loop? "For every NULL record, use products.product_id to construct the INSERT INTO for products_discounts." – Chris76786777 Feb 14 '17 at 19:46
6

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

  • 10 and 'Top 10 Product' are static values for this task. The only dynamic value for the INSERT statement is product_Id. – Chris76786777 Feb 14 '17 at 21:38
  • @idon'twearsuits I think you should update the question to be clearer about the end result you want, then. The "my own values" bit in the title, as well as your description of what you do now, sounds like you are using eyeball and spit to pick discount percentages manually. – Aaron Bertrand Feb 14 '17 at 21:46
  • I honestly don't know what I can do with the title but I've changed the question a lot. What do you think? – Chris76786777 Feb 15 '17 at 5:22
  • @idon'twearsuits Don't forget you also need to deal with the opposite scenario - products in the discount table that are no longer in the top 10. – Aaron Bertrand Feb 15 '17 at 5:26
  • 1
    I had the chance to get back and try this and I think this is the better answer. It's more succinct but, more importantly to me, states how many rows were affected rather than repeating "1 row(s) affected". – Chris76786777 Feb 21 '17 at 22:36
6

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;
  • 1
    Yes this. I was going to post it myself, but it didn't sound like exactly what he wanted to do. But this is exactly how to do it. You could even go so far as to create a variable to contain the statements and then execute with sp_executesql – Steve Mangiameli Feb 14 '17 at 19:52
2

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
  • This would work for my understanding of the problem (I was about to post a similar answer) except that discount_amount should come from the Products_Discounts table rather than always being set to 10. But I am pretty sure it will work as the set of "products without discounts" will be generated before the INSERT statement. – seventyeightist Feb 14 '17 at 21:13
  • 1
    @seventyeightist I wasn't sure, sounded almost like the OP was manually hand-applying the specific discount (hence "my own values"). – Aaron Bertrand Feb 14 '17 at 21:14
  • @AaronBertrand You're right, the discount_amount will be null in that situation because product_id didn't match. My bad! – seventyeightist Feb 14 '17 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.