INSERT IGNORE duplicates

[code green]

I am updating a table 'price_hist' inside a stored procedure in which I have placed a unique key constraint across three fields.

price_list, part_no, price

In MySql the INSERT IGNORE command is useful on bulk inserts when a duplicate key is encountered as it prevents the query failing.
How is similar achieved in MsSql?

[ck9663]

IGNORE meaning, not insert or insert still but not give error?

-- CK

[code green]

I do not want to insert duplicate records,
but I don't want the procedure failing if a duplicate is encountered.
So the query needs to IGNORE the error, skip the offending record and continue.
Here is part of the procedure so far

DECLARE @lastdate AS DATETIME
SELECT @lastdate = MAX(date_from)F ROM plis_hist;
SET DATEFIRST 3 -- Set the first day of the week to Wednesday

/* Now copy prices from plis to plis_hist */
INSERT INTO plis_hist
(price_list, part, date_from, volume,dt_creat ed, last_upd_user,
price, week_no)
(
SELECT pls.price_list, pls.part, pls.date_from, pls.volume,
pls.dt_created, pls.last_upd_us er, pls.price,
dbo.FiscalWeek( '01',pls.date_f rom) week_no
FROM plis
LEFT JOIN plis_hist hist
ON (hist.part = pls.part
AND hist.price_list = pls.price_list
AND (hist.price != pls.price OR hist.part IS NULL))
AND pls.date_from > @lastdate
);

Unfortunately it is possible for users to update the plis table when nothing but the date_from changes so price_list, part, volume, price remain the same.
As these are my CONSTRAINT fields a CONSTRAINT error is thrown

[ck9663]

I did not touch your first JOIN and just add a new one. The where clause will filter out those that are already existing ie the same values with those CONSTRAINT fields

 
INSERT INTO plis_hist
(price_list, part, date_from, volume,dt_created, last_upd_user, price, week_no)
(
SELECT pls.price_list, pls.part, pls.date_from, pls.volume,
pls.dt_created, pls.last_upd_user, pls.price,
dbo.FiscalWeek('01',pls.date_from) week_no
FROM plis
LEFT JOIN plis_hist hist
   ON (hist.part = pls.part AND hist.price_list = pls.price_list AND (hist.price != pls.price OR hist.part IS NULL)) AND pls.date_from > @lastdate
left join plis_hist hist2
   on (plis.part = hist2.part and plis.price_list = hist2.price_list and plis.volume = hist2.volume and plis.price = hist2.price)
where hist2.part is null
);
 
 

[code green]

Thanks for that idea ck9663.
I was thinking along similar lines using a
WHERE NOT IN (SELECT price_list,part ,price FROM ...) sub-query.
But your JOIN is much better performance wise.