mardi 4 août 2015

Create table if not exists syntax db2

I wish to write a SQL script that will check whether the table/sequence exists or not before create the table/sequence.

I tried Google and get some solution that work for other people but not work for me:

Method 1:

SELECT *
FROM   tableA
WHERE  EXISTS
 (SELECT * from tableB);

This is work with select statement. Then I try with create table statement:

CREATE TABLE "SMEADM"."JXTEST"  (
          "ACCOUNTSENTRYID" BIGINT NOT NULL  )   
         IN "DATATBSP"
WHERE  EXISTS
 (SELECT * from tableB);

This will hit error 42601.

Method 2:

CREATE TABLE IF NOT EXISTS "SMEADM"."JXTEST"  (
          "ACCOUNTSENTRYID" BIGINT NOT NULL  )   
         IN "DATATBSP" ; 

This also bring me to error 42601.

Method 3:

begin
  declare continue handler for sqlstate '42710' begin end;
  execute immediate 'CREATE TABLE "SMEADM"."JXTEST"  (
          "ACCOUNTSENTRYID" BIGINT NOT NULL  )   
         IN "DATATBSP"';
end

And this also bring me to error 42601.

Kindly advise.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire