3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Panadol Chong
  • 1,793
  • 13
  • 54
  • 119

3 Answers3

1

The correct way is your third option, however you have to write it correctly. If you read the output message, you got that there is an invalid character (the 42601 SQL state). This is due to the string that does not have and ending character. You cannot have a multi line string, instead you have to create multiple lines and concatenate them.

When I run:

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

I got:

begin
  declare continue handler for sqlstate '42710' begin end;
  execute immediate 'CREATE TABLE "SMEADM"."JXTEST"  ('
db2 (cont.) => db2 (cont.) => db2 (cont.) =>          || ' "ACCOUNTSENTRYID" BIGINT NOT NULL  )  '  ;
db2 (cont.) => end@
DB20000I  The SQL command completed successfully.
AngocA
  • 7,655
  • 6
  • 39
  • 55
  • Actually I am still hitting `42719` erro. Begin declare continue handler for sqlstate '42710' begin end DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "end" was found following "lstate '42710' begin". Expected tokens may include: "". LINE NUMBER=2. SQLSTATE=42601 – Panadol Chong Aug 10 '15 at 03:53
  • I'm a couple of years late but @panadol-chong whatever you were running your sql in was not executing the lines as a single statement. – Dave Bower Nov 23 '17 at 15:03
0

You can check whether the table exist or not using sysibm.systables, for sequence you can use SYSIBM.SYSSEQUENCES.

DECLARE
    v_tbl_cnt NUMBER(1,0);
    v_seq_cnt NUMBER(1,0);
BEGIN
    select count(1) into v_tbl_cnt 
    from sysibm.systables
    where owner = 'your_schema'
    and type = 'T'
    and name = 'your_table';

    IF (v_tbl_cnt = 0) THEN --table not exists
        execute immediate 'create table ...'; --create table
    END IF;

    select count(1) into v_seq_cnt 
    from SYSIBM.SYSSEQUENCES
    where owner = 'your_schema'
    and name = 'your_sequence';

    IF (v_seq_cnt = 0) THEN --sequence not exists
        execute immediate 'create sequence ...'; --create sequence
    END IF;
END;
Praveen
  • 8,945
  • 4
  • 31
  • 49
-6

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }

check in the below link i am not sure whether it will work or not please try and see. http://dev.mysql.com/doc/refman/5.1/en/create-table.html

RAVI507
  • 129
  • 1
  • 1
  • 8