By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

INSERT IGNORE duplicates

code green
Expert 100+
P: 1,726
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?
Mar 24 '09 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
IGNORE meaning, not insert or insert still but not give error?

-- CK
Mar 24 '09 #2

code green
Expert 100+
P: 1,726
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)FROM 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_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
);
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
Mar 24 '09 #3

ck9663
Expert 2.5K+
P: 2,878
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. INSERT INTO plis_hist
  3. (price_list, part, date_from, volume,dt_created, last_upd_user, price, week_no)
  4. (
  5. SELECT pls.price_list, pls.part, pls.date_from, pls.volume,
  6. pls.dt_created, pls.last_upd_user, pls.price,
  7. dbo.FiscalWeek('01',pls.date_from) week_no
  8. FROM plis
  9. LEFT JOIN plis_hist hist
  10.    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
  11. left join plis_hist hist2
  12.    on (plis.part = hist2.part and plis.price_list = hist2.price_list and plis.volume = hist2.volume and plis.price = hist2.price)
  13. where hist2.part is null
  14. );
  15.  
  16.  
Mar 24 '09 #4

code green
Expert 100+
P: 1,726
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.
Mar 25 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.