Insert multiple rows, but for each row check if it does not already exist-CodePudding

I am looking to insert multiple rows if they don't EXIST in the target table. But I'm not sure how do this with the following code:

INSERT INTO sales.promotions(
    promotion_name,
    discount,
    start_date,
    expired_date
)
VALUES
('2019 Summer Promotion', 0.15, '20190601', '20190901'),
('2019 Fall Promotion',   0.20, '20191001', '20191101'),
('2019 Winter Promotion', 0.25, '20191201', '20200101');

Because the WHERE NOT EXIST clause would apply to ALL rows whereas I need to do it row by row:

WHERE NOT EXISTS (SELECT * FROM sales.promotions
    WHERE promotion_name = 'Winter Promotion');

Sorry if this is painfully obvious, not too good at SQL and I'm not really sure how to word this question for proper research.

CodePudding user response：

It is possible to use table value constructor with exists:

INSERT INTO sales.promotions(promotion_name, discount, start_date, expired_date)
SELECT *
FROM (VALUES
    ('2019 Summer Promotion', 0.15, '20190601', '20190901'),
    ('2019 Fall Promotion',   0.20, '20191001', '20191101'),
    ('2019 Winter Promotion', 0.25, '20191201', '20200101')
) AS values_tobe_inserted(promotion_name, discount, start_date, expired_date)
WHERE NOT EXISTS (
    SELECT *
    FROM sales.promotions
    WHERE sales.promotions.promotion_name = values_tobe_inserted.promotion_name
)