DB2 Create multiple tables with same DDL with table names coming from a query-CodePudding

I need to create multiple tables in my schema with the exact same DDL. For that I would usually use a command:

CREATE TABLE SCHEMA.XYZ_STORE_TABLE AS SCHEMA.EFG_STORE_TABLE

This part is easy. Now I have to repeat the same process for 1000s of tables. To make my life easier, I have created a table which will store all the table names that are supposed to be created, whose structure looks like:

CREATE TABLE SCHEMA.ABC_PROCESS(
    PROCESS_CD VARCHAR(32),
    PROCESS_NAME VARCHAR(100),
    PROCESS_STORE_TABLE_NAME VARCHAR(100)
)

Then I can query SELECT PROCESS_STORE_TABLE_NAME FROM SCHEMA.ABC_PROCESS and get the table names to be created.

| PROCESS_STORE_TABLE_NAME |
| ------------------------ |
| ABC_STORE_TABLE          |
| HIJ_STORE_TABLE          |

Now I could write a Java code which will get these table names, store it in an ArrayList and then execute the CREATE TABLE scripts on each element of that ArrayList from Java code.

Is there a simpler way to do it using SQLs itself without writing Java code? (PS. You can assume that the table names coming from the query don't already exist)

CodePudding user response：

Yes, there is such a way:

SELECT CONCAT(
    'CREATE TABLE SCHEMA.XYZ_STORE_TABLE AS SCHEMA.',
    PROCESS_STORE_TABLE_NAME,
    ';'
)
FROM SCHEMA.ABC_PROCESS;

This will generate a CREATE command for each table name you find in ABC_PROCESS.

CodePudding user response：

You may use dynamic sql inside a compound statement.

--#SET TERMINATOR @
CREATE TABLE EFG_STORE_TABLE (I INT)@

CREATE TABLE ABC_PROCESS
(
  PROCESS_STORE_TABLE_NAME VARCHAR(100)
)@

INSERT INTO ABC_PROCESS (PROCESS_STORE_TABLE_NAME)
VALUES
  'ABC_STORE_TABLE'
, 'HIJ_STORE_TABLE'
@

BEGIN
  FOR C1 AS
    SELECT 
      'CREATE TABLE '
    || PROCESS_STORE_TABLE_NAME
    || ' LIKE EFG_STORE_TABLE'
      AS CMD
    FROM ABC_PROCESS
  DO
    EXECUTE IMMEDIATE C1.CMD;
  END FOR;
END
@

fiddle