CODEWITHSUNDEEP
sqlsql-serversql-insert
MattHodson
MattHodson

Reputation: 786

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

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.

Upvotes: 4

Views: 2654

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272006

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
)

Upvotes: 7

Related Questions