Can columnar tables created in one schems be moved to another schema?

[joseph_gonzales]

Hello HANA Friends:

Can columnar tables created in one schema be moved to another schema?

I've loaded a number of tables to a schema in SAP HANA.

I was then informed that these tables should have been load in a different schema.

I need some directions or guidance on how to move tables from one schema to another.

Any advice or comment would be appreciated.

Regards,

Joe Gonzales

856 912 1136

[former_member184768]

How about this:

Run the following SQL statement to generate a script which can be executed in SQL editor. Assuming that the source Schema is YYY and Target schema is XXX, you can use "Create table like". You then the modify the same SELECT statement to generate DROP table script and remove the tables from the YYY schema after confirming that all the tables are created with data successfully.

select 'CREATE COLUMN TABLE "XXX"'||'."'||table_name||'" like "'||schema_name||'"."'||table_name||'" WITH DATA;' from tables where schema_name = 'YYY';

Regards,

Ravi

[rindia]

Hi Joe,

It is possible to move tables from one schema to another schema. Follow below steps

1.File menu -> Export

2. Expand SAP HANA Studio -> Select Catalog Objects -> Next

3. Select the system which contains schema tables -> Next

4. In find, type the schema name -> Select the schema -> Add

5. Again Select the schema on right side -> Next

6. Export Selection format - CSV, Export: Catalog + Data

     Export Location, Check eport Default directory -> Finish

Export process is completed. Now Import from the same location.

1. Select the system in which the schema tables you want

2. File -> Import

3. Expand SAP HANA Studio -> Catalog Objects -> Next

4. Import location, Check the box, Use default directory -> Next

5. Enter the schema name in "Type name to find a catalog object" and select the tables you want to move and click Add

6. Select all the tables on right column -> Next

7. Import selection : Import -  Catalog and Data

8. Finish

Remember that you need to have access to do so.

Try your luck.

Regards

Raj

[Former Member]

You'd likely get better performance keeping everything in memory. Here's an approach in that vain:

1) Get your table definition: CALL GET_OBJECT_DEFINITION('<SCHEMA_NAME>', 'TABLE_NAME');

2) Copy and paste the resulting definition and replace schema with target schema name.

3) Load new table from old table:

SELECT * FROM OLD_SCHEMA.TABLE INTO NEW_SCHEMA.TABLE

I'd be curious to see how the performance compares.

Cheers,

Jody