1

I have seen scripts working for SQL server using If not exists.

But I couldn't execute similar script in DB2.

Below link is just an example but not exactly my requirement

DB2 Drop table if exists equivalent

omkar sirra
  • 696
  • 10
  • 28
  • https://stackoverflow.com/questions/17274459/db2-insert-row-if-not-exist-with-value-from-parameter Please try this. – Shadab Siddiqui Jul 18 '18 at 15:52
  • 1
    Db2 has a MERGE statement that is even better than those scripts. – data_henrik Jul 18 '18 at 15:58
  • Try the SP db2perf_quiet_drop referenced here https://dba.stackexchange.com/questions/9535/db2-equivalent-for-mysqls-drop-if-exists – Paul Vernon Jul 18 '18 at 16:25
  • Possible duplicate of [DB2 insert row if not exist with value from parameter](https://stackoverflow.com/questions/17274459/db2-insert-row-if-not-exist-with-value-from-parameter) – Stavr00 Jul 18 '18 at 17:00
  • Your question title does not match the body. Are you after inserting rows into a table, or dropping/creating a table? – mustaccio Jul 18 '18 at 17:10
  • Trying to insert a row . Conditional Insert statement @mustaccio – omkar sirra Jul 18 '18 at 17:14

1 Answers1

3

Unfortunately you did not provide your table structure and sample data, so I can only guess what they look like, but, as suggested by data_henrik, you should probably use the MERGE statement, something like:

MERGE INTO target_table tgt
USING TABLE (VALUES ( 42, 'foo', 'bar')) AS src ( id, val1, val2 )
ON tgt.id = src.id
WHEN NOT MATCHED
THEN INSERT ( id, col1, col2 ) VALUES ( src.id, src.val2, src.val1 )

The above assumes that target_table.id is how you determine that "a row doesn't exist". Adjust the condition to include all key columns as necessary.

If your Db2 version, which for some reason you also chose not to disclose, does not yet support the MERGE statement, you can simulate the behaviour using the standard INSERT statement:

INSERT INTO target_table tgt ( id, col1, col2 )
SELECT FROM TABLE (VALUES ( 42, 'foo', 'bar')) AS src ( id, val1, val2 )
WHERE NOT EXISTS (
  SELECT FROM target_table WHERE id = src.id
)
mustaccio
  • 18,234
  • 16
  • 48
  • 57