SQL Server (T-SQL) Insert if Don't Exist.

Question

Question

How can I alter the SQL code below to ensure that the inserts into "dbo.tblBootStrapperInstruments" only occur if they don't already exist?

Code

INSERT INTO dbo.tblBootStrapperInstruments (CCY, Instrument, Tenor)
SELECT CCY,Instrument,Gridpoint FROM dbo.Feed_Murex_Intraday_DF mx WHERE NOT EXISTS 
(SELECT * FROM dbo.tblBootStrapperInstruments bs
WHERE bs.CCY = mx.CCY
AND bs.Instrument = mx.Instrument
AND bs.Tenor = mx.Gridpoint) 

Answer 1

What RDBMS are you using? MySQL? MSSQL? SQLite?

In MySQL you have two choices. You can add a unique index on your insert values and opt for a REPLACE statement or use ON DUPLICATE KEY UPDATE.

Answer 2

Here's how I'd do it.

INSERT INTO dbo.tblBootStrapperInstruments (CCY, Instrument, Tenor) 
SELECT mx.CCY,mx.Instrument,mx.Gridpoint FROM dbo.Feed_Murex_Intraday_DF mx
LEFT OUTER JOIN dbo.tblBootStrapperInstruments bs 
ON bs.CCY = mx.CCY 
AND bs.Instrument = mx.Instrument 
AND bs.Tenor = mx.Gridpoint
WHERE bs.CCY IS NULL

Answer 3

If your database flavour and version supports MERGE you could use that (MERGE was added to SQL Server 2005). The syntax is a little clunky, because it is really meant for data loading by selecting from another table.

This examples uses Oracle (because that's what I've got).

SQL> merge into customer
  2      using ( select 6 as id
  3                     , 'Ella' as forename
  4                     , 'Chip' as surname
  5                     , '1234 Telepath Drive' as address_1
  6                     , 'Milton Lumpky' as address_2
  7                     , 'ML1 4KJ' as postcode
  8                    ,  add_months (sysdate, -235) as date_of_birth
  9               from dual ) t
 10      on (t.id = customer.id )
 11  when not matched then
 12      insert (id, forename, surname, address_1, address_2, postcode, date_of_birth)
 13      values (t.id, t.forename, t.surname, t.address_1, t.address_2, t.postcode, t.date_of_birth)
 14  /

1 row merged.

SQL> commit;

Commit complete.

SQL>

If we run the same query again (ID is the primary key) it doesn't fail, it just merges one row...

SQL> merge into customer
  2      using ( select 6 as id
  3                     , 'Ella' as forename
  4                     , 'Chip' as surname
  5                     , '1234 Telepath Drive' as address_1
  6                     , 'Milton Lumpky' as address_2
  7                     , 'ML1 4KJ' as postcode
  8                    ,  add_months (sysdate, -235) as date_of_birth
  9               from dual ) t
 10      on (t.id = customer.id )
 11  when not matched then
 12      insert (id, forename, surname, address_1, address_2, postcode, date_of_birth)
 13      values (t.id, t.forename, t.surname, t.address_1, t.address_2, t.postcode, t.date_of_birth)
 14  /

0 rows merged.

SQL>  

Compare and contrast with a bald INSERT ...

SQL>   insert into customer (id, forename, surname, address_1, address_2, postcode, date_of_birth)
  2  values ( 6, 'Ella', 'Chip', '1234 Telepath Drive', 'Milton Lumpky', 'ML1 4KJ',add_months (sysdate, -235) )
  3  /
    insert into customer (id, forename, surname, address_1, address_2, postcode, date_of_birth)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.CUS_PK) violated


SQL>