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.
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.