How to copy tables from one schema to another schema

[Former Member]

How can i copy tables from schema A to schemaB both schemas are present in same system.

Also how can i getenaret create table script, i right clicked but under generate scripts i did'nt see create.

In sql server you do have those options to generate scripts.

Is there a mass tables copy to another schema available, i found csv export mass tables option i found.

thanks a lot for the helpful info.

[hai_murali_here · Accepted solution]

Hi,

To copy single table through SQL, you can use

create column table <Schema Name>.<New Table> as (select * from "Schema Name"."<Old Table>")

To copy set of tables,you have to use Export/Import feature of Quick Launch

To see the definition script of the table,you can right click the table and say "Open Definition" and again right click in the opened definition and say "Export SQL".This will show the table definition.

Hope this helps.

Rgds,

Murali

[Former Member · Accepted solution]

Hi Murali,

I tried to export the whole schema content to the client, and then import the schema content to another schema. But the error said "Can't import due to table already exists: line 1 col 19 (at pos 18)". Can you help me with it?

Thank you!

Regards,

Bella

[hai_murali_here · Accepted solution]

Hi,

When you export the schema with the content,the schema name would be maintained.So if you try to import the same schema name will be referred and thatsy it says the table already exists.

After export rename the schema to the new schema (where you want to import) and then do the import

Refer this : http://scn.sap.com/thread/3409793

Rgds,

Murali

[Former Member · Accepted solution]

Hi Murali,

With your tip and my colleague's help, I tried again with the statement below and it works! Thanks!

1. create a temp folder in server, like /tmp/SFLIGHT

2. execute the statements as below:

EXPORT "SFLIGHT"."*" AS BINARY INTO '/tmp/SFLIGHT' WITH REPLACE SCRAMBLE THREADS 10;

IMPORT "SFLIGHT"."*" AS BINARY from '/tmp/SFLIGHT' with RENAME SCHEMA "SFLIGHT" TO "SHANA";

BR.

Bella

[former_member722 · Accepted solution]

This works for me, Thanks!

[pandiri_balaraju2]

This message was moderated.

[Former Member]

This message was moderated.

[Prabhith]

Hi Raj,

The IMPORT command allows you to change the target schema name from SP6 , I believe

Br

Prabhith

[Former Member]

Hello Raj,

You can use Export/Import feature to load the tables from one schema to another schema.

If you do export,you will get ".SQL" and also "Data" to your destined folder from where you can again use "Import" to load the data to specified Schema.

Please see this link on Import/Export feature in HANA:

http://scn.sap.com/docs/DOC-26381

Regards,

Krishna Tangudu

[former_member195156]

Hi,

To get the SQL to create the table, right click on the table name in the Navigator window of the Studio and select Open Definition.  In the main window showing the table definition, right click and select Export SQL.

The only way I'm aware of to copy tables is to use export/import.  Note that export will also generate a file called create.sql which contains the SQL statement to create the table, so this is another way to get this information.

Cheers,

Jody

[rama_shankar3]

Another way of copying tables can be done quickly using BODS (aka data services). Personally, I recommend that you start getting used to SQL. In HANA, SQL is quicker to get things done!

Regards,

Rama

[former_member184768]

In HANA you have an option to create the table with CREATE TABLE ... AS <subquery> option.

Unfortunately I don't have a ready connection to HANA this moment, but I think in the table definition, there is an option to see the SQL statement, which is similar to SQL Server option of CREATE TABLE script generation.

Personally, I'd prefer to create a "CREATE TABLE ... AS ... <subquery> " script myself from the data from TABLES view and then run the generated script.

Ravi