All Forums Database
20 Oct 2015
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
 
 
 
 
 

 

 

You must sign in to leave a comment.