1

I have one insert statement that inserts multiple rows at once using VALUES and each set of values separated by a comma like this:

INSERT INTO link_instance_message (instance_id, message_id) 
VALUES 
( (select instance_id from instance where instance_key = '2222d4a5-6ec6-4a35-a24d-2dc3980b2e29'), 26),
( (select instance_id from instance where instance_key = '2323d4a5-6ec6-4a35-a24d-2dc3980b6969'), 26)

However, if one of them already exists it fails to do the entire thing due to a duplicate key constraint on the link_instance_message table. I'm wondering how I can have the insert statement still be one insert call but ignore inserting let's say instance_key = '2222d4a5-6ec6-4a35-a24d-2dc3980b2e29' because it already exists but not ignoring instance key = '2323d4a5-6ec6-4a35-a24d-2dc3980b6969' because it doesn't already exist.

I really want to avoid having to having to make a separate SQL calls for each insert because there could up to 1,000 rows inserted and I don't think that's particularly efficient but I'm not sure.

Brian T Hannan
  • 3,925
  • 18
  • 56
  • 96
  • 2
    you can write the records to a temp table, then join the temp table with link_instance_message table. – Eric K Yung Jul 21 '14 at 21:34
  • @Eric.K.Yung: +1 but `LEFT OUTER JOIN` or `NOT EXISTS` would be more appropriate if he wants to insert only non-existing `instance_key`s. – Tim Schmelter Jul 21 '14 at 21:42

0 Answers0