Creating replica of one schema to another schema in Exasol

Question

Can anyone help me with creating a replica in EXASOL i.e. I need to copy all the tables including Views,Functions and Scripts from one schema to another schema in the same server. For Eg.: I want all the data from Schema A to be copied not moved to Schema B. Many thanks.

Answer 1

Thank you wildraid for your suggestion :)

In-order to copy DDL of all the tables in schema, I've got a simple way that will give us the DDLs for all the tables :

select t1.CREATE_STATEMENT||t2.PK||');' from (Select C.COLUMN_TABLE,‘CREATE TABLE ’ || C.COLUMN_TABLE ||'(' || group_concat( ‘“’||C.COLUMN_NAME||'“' || ' ' || COLUMN_TYPE || case when (C.COLUMN_DEFAULT is not null and C.COLUMN_IS_NULLABLE=‘true’) or(C.COLUMN_DEFAULT<>‘NULL’ and C.COLUMN_IS_NULLABLE=‘false’) then ' DEFAULT ' || C.COLUMN_DEFAULT end || case when C.COLUMN_IS_NULLABLE=‘false’ then ' NOT NULL ' end order by column_ordinal_position) CREATE_STATEMENT from EXA_ALL_COLUMNS C where upper(C.COLUMN_SCHEMA)=upper(‘Source_Schema’) and column_object_type=‘TABLE’ group by C.COLUMN_SCHEMA, C.COLUMN_TABLE order by C.COLUMN_TABLE ) t1 left join (select CONSTRAINT_TABLE,‘, PRIMARY KEY (’ ||group_concat(‘“’||COLUMN_NAME||'“' order by ordinal_position) || ‘)’ PK from EXA_ALL_CONSTRAINT_COLUMNS where constraint_type=‘PRIMARY KEY’ and upper(COnstraint_SCHEMA)=upper(‘Source_Schema’) group by CONSTRAINT_TABLE ) t2 on t1.COLUMN_TABLE=t2.constraint_table order by 1;

Replace the Source_Schema with your schema name and it will generate the Create statement that you can run on the EXAplus.

For copying the data, I have used the same way that you've mentioned in step 2.

Answer 2

Ok, this question consists of two smaller problems.

1) How to copy DDL of all objects in schema

If you need to copy only small amount of schemas, the fastest way is to use ExaPlus client. Right click on schema name and select "CREATE DDL". It will provide you with SQL to create all objects. You may simply run this SQL in context of new schema.

If you have to automate it, you may take a look at this official script: https://www.exasol.com/support/browse/SOL-231

It creates DDL for all schemas, but it can be adapted to use single schema only.

2) How to copy data

This is easier. Just run the following SQL to generate INSERT ... SELECT statements for every table:

SELECT 'INSERT INTO <new_schema>.' || table_name || ' SELECT * FROM <old_schema>.' || table_name || ';'
FROM EXA_ALL_TABLES
WHERE table_schema='<old_schema>';

Copy-paste result and run it to make the actual copy.