Copying the Tables(having identity columns) with Data from one Db to another DB-

Database
Enthusiast

Copying the Tables(having identity columns) with Data from one Db to another DB-

Hi all,

there is a requirement of copying all the tables with data from one db (may be dev) to another db ( Test) .

It can be done as below :

a) Utilizing the metadata tables for creating the automated  create table statements  as below :

SEL 'CREATE MULTISET TABLE  ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ' AS  ' ||  'dev_a.' || TRIM(TABLENAME)

|| ' WITH DATA AND STATS; '

FROM DBC.TABLES WHERE DATABASENAME ='dev_b';

this generates the list of the tables but this will not work for the tables having the identity column key, during the execution in database.

There is a workaround found in order to handle it in one of the forums that :

create table db.tablebackup as 
(select * from (select * from db.table) x)
with data;

but as mentioned in the forum , it will have the another issue that back up table will have the default first column as the primary index .

Is there any other way of handling the automatic scripts for the table with identity columns?

Thanks!

Nishant