How to copy SQL objects (tables, views & procedures) from one schema to another schema in the HANA same server?

[Former Member]

Hello,

I need to dynamically provision Schema/Catalog, load data, analyze data, generate reports and drop the catalogs/schema from the server. 

I got a schema called "MasterCatalog" having 10 tables (most of them are empty), 3 views and 10 stored procedures. 

Now I want to copy the tables and procedures from MasterCatalog to newly created (empty) schema problematically.

First I create an empty schema using "CREATE SCHEMA" statement. Then copy the objects from master catalog to newly created schema.

For that, I tried Export & Import SQL commands. Using export command, i tried to export SQL objects onto a local server folder then tried to import the objects into newly created schema.

But it is trying to import SQL objects into the same source schema. There is no provision to specify the target schema on Import statement.

Any idea how to copy objects from one schema to another schema on the same server?

Regards,

TK

[lbreddemann · Accepted solution]

Hi there!

As of SPS 6 the IMPORT command allows for changing the target schema name(s):

Syntax

 IMPORT <object_name_list> FROM <path> [WITH <import_option_list>] [AT [LOCATION] <indexserver_host_port>]

WITH <import_option_list>
 <import_option_list> ::= <import_option> [{, <import_option>}]
 <import_option> ::= REPLACE | CATALOG ONLY | DATA ONLY |  NO DEPENDENCIES | THREADS <number_of_threads> | RENAME SCHEMA <rename_schema_list>

 RENAME SCHEMA <rename_schema_list> <rename_schema_list> ::= <rename_schema_token> [{, <rename_schema_list>}] <rename_schema_token> ::= <source_schema> TO <target_schema> 

It is possible to rename the objects' schema during import. Multiple schemas can be renamed by specifying multiple <rename_schema_token>. It is not allowed to specify the same schema as both <source_schema> and <target_schema> in same or different <rename_schema_token>.

This is not yet in the released documentation, but will be soon.

- Lars

[Former Member · Accepted solution]

Hi Lars,

Good news, which HANA revision can we use rename schema during import? Whether it only support import command?

Thanks.

Regards,

Jerry

[lbreddemann · Accepted solution]

That command works as of SPS 6 (rev. 60).

I have no clue what your second question means.

- Lars

[Former Member · Accepted solution]

Hi Lars,

My second question is whether we can use other way or tool except import command method.

Thanks.

Regards,

Jerry

[lbreddemann · Accepted solution]

Hi Jerry,

up to now (rev. 63) there's no GUI option for that.

So you can use that for server side import only at the moment.

- Lars

[Former Member · Accepted solution]

Hi Lars,

Thank you for your information, got it.

Thanks,

Regards,

Jerry

[Former Member · Accepted solution]

Hi Lars,

I have some difficulties in the syntax. seems its not complete?

Could you please give me an example?

import from schema A to schema B;

option: catalog and data

threads:1

Thank you so much!

regards,

Bella

[lbreddemann · Accepted solution]

Hi Bella,

please post the exact command you used and the error message you get for it.

Cheers,

Lars

[Former Member · Accepted solution]

Hello Lars,

Thanks for your idea! Finally it works!

The statements used to copy content from one schema to another schema are 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";

Note:  /temp/SFLIGHT is a temp folder in server; "*" can be a table or all content

BR.

Bella

[former_member214968]

Hello Team,

We have migrated SQL Server 2008 to SAP HANA Instance. We deployed the Tables in SAP HANA. But we would like to know the best alternative solution to deploy Stored Procedures in SAP HANA. We are not using any ETL tools (BODS). So, kindly let me know the alternative solutions.

Best Regards

[Former Member]

Praveen,

Please find below the article on "How to Convert SQL from the Microsoft SQL Server Database to the SAP HANA™ Database".

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/801c67ef-ebe0-3010-3085-a7fc864fc...

Regards,

TK

[former_member184768]

Hi Thandava,

If your number of tables, views and procedures are limited, you might keep them in a single script and run the script every time a new schema is created. Please ensure that your object names in CREATE VIEW, CREATE PROCEDURE and within the procedure are not prefixed with the schema name,

You can create tables using CREATE TABLE ... like...with data; option, but it will not work for Views and procedures.

I'd recommend using the CREATE objects script and run it after the schema creation.

Regards,

Ravi

[Former Member]

Hello Ravi,

Thanks for your advice.

Is there a way in HANA to execute the scripts problematically? I didn't see any other approach other than Export.

I need to dynamically/problematically provision a schema and copy SQL objects, load data and analyze it.

Regards,

TK

[former_member184768]

If you have access to HANA command line utility HDBSQL, then you can use it to run your scripts programatically. Please check if this option is feasible for your requirement.

Regards,

Ravi

[former_member184768]

Alternate approach is to use EXEC SQL / Dynamic SQL. But it depends upon the programming language you are using and may have performance impact.

Regards,

Ravi

[Former Member]

Hi TK,

The "import/export" function of HANA studio, it is only support for same schema and cannot change it during import, it has no special method to copy tables and SP on HANA studio so far, you may use BODS tool.

besides, you can modify your export *.sql script to modify schema by manually, and exec on sql console, or you can use sql script "like" key word to create table, such as create table "target schema name"."table name" like "original schema name"."table name" with data.

Hope this can help you.

Regards,

Jerry

[Former Member]

Jerry,

Thanks for your advise.

It perfectly works for tables using like keyword.  But it doesn't work with procedures.

And i also checked with my BODS team, it is not possible to dynamically create procedures on the server.

Regards,

TK

[Former Member]

Hi TK,

Yes, right, the like keyword only support table, not support procedure and view, sorry to missed content and confuse you.

Do you check and consider PowerDesigner tool can do or not, I remembered HANA database can use PowerDesigner tool.

Please check below link for PowerDesigner and HANA.

Hope this can help you.

http://www.saphana.com/docs/DOC-3337