CODEWITHSUNDEEP
sqlcreate-tabledb2-400
Dor Yaloz
Dor Yaloz

Reputation: 1

Create table/column if not exists for DB2 - Not using a sp, just manual query

I tried using the SQL syntax:

CREATE TABLE IF NOT EXISTS PWRNTCDT.customers_contacts(
    customer_id varchar(10) NOT NULL DEFAULT '',
  contact_index INT NOT NULL,
  is_primary SMALLINT NOT NULL,
  f_name varchar(40) DEFAULT NULL,
  l_name varchar(40) DEFAULT NULL,
  job_title varchar(60) DEFAULT NULL,
  phone varchar(30) DEFAULT NULL,
  mobile varchar(30) DEFAULT NULL,
  email varchar(254) DEFAULT NULL
);

and I got an error indicating a syntax error on the "exists" part. Couldn't find anything online. Thanks!

Upvotes: 0

Views: 1131

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

There is no such a CREATE TABLE functionality in DB2 for IBM i.
There is CREATE OR REPLACE but not CREATE IF NOT EXISTS.
Refer to the CREATE TABLE statement description in the documentation.

But you may use Dynamic compound statement for this.

-- Use some another statement terminator in your tool you run this statement from like "@" as below
-- Or don't use this statement terminator at all, if you run the statement from some external program
BEGIN
  IF NOT EXISTS
  (
  SELECT 1 
  FROM QSYS2.SYSTABLES
  WHERE TABLE_SCHEMA='PWRNTCDT' AND TABLE_NAME='CUSTOMER_CONTACTS'
  ) THEN

  CREATE TABLE PWRNTCDT.customers_contacts
  (
  customer_id varchar(10) NOT NULL DEFAULT '',
  contact_index INT NOT NULL,
  is_primary SMALLINT NOT NULL,
  f_name varchar(40) DEFAULT NULL,
  l_name varchar(40) DEFAULT NULL,
  job_title varchar(60) DEFAULT NULL,
  phone varchar(30) DEFAULT NULL,
  mobile varchar(30) DEFAULT NULL,
  email varchar(254) DEFAULT NULL
  );

  END IF;
END@

Upvotes: 1

Related Questions