insert data from one Schema table to another Schema table

[a_ahmad]

Hello,

Let's say I have AAA.table1 and ZZZ.table1

where AAA and ZZZ are two different schemas and

table1 is of same definitions/structure under both schemas

Data records are inserted into AAA.table1, now I want to insert records from AAA.table1 into ZZZ.table1

what would be the syntax?

I have checked and there exists an SQL statement:

'creating new table based on another old table alongwith its records' and

result: is a new table same structure as old table with records.

But I am looking for some kind of insert syntax

Best regards

Ahmad

[Former Member · Accepted solution]

Hi Ahmad,

Try this:

INSERT INTO "ZZZ"."table1" SELECT * FROM "AAA"."table1";

If you want to only insert select columns:

INSERT INTO "ZZZ"."table1" (A,B) SELECT A,B FROM "AAA"."table1";

Thanks,

Anooj

[a_ahmad · Accepted solution]

hi Anooj,

when I exe this SQL statement then i get

SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

in my scenario: user ZZZ want to copy data from user AAA (between Schemas) same system

both users have SYSTEM, PUBLIC, IC_MODELER, privileges

what other authorizaitons are necessary?

Thanks

Ahmad

[Former Member · Accepted solution]

Hi Ahmad,

You need SELECT authorisation on schema AAA and INSERT authorisation on schema ZZZ added to the user that is running the SQL.

For e.g. If SYSTEM is running the query, add AAA and ZZZ schemas under SQL privileges for SYSTEM and give them the SELECT and INSERT rights respectively.

Let me know if you still have problems.

Thanks,

Anooj

[Former Member · Accepted solution]

Ok, I don't think I explained it well last time.

I am taking this scenario - You have three users SYSTEM, AAA, & ZZZ each with their own schemas. You have a table created in AAA and populated with data. You have a similar table created in ZZZ but want to populate it with the data from the table in AAA.

Firstly, login as SYSTEM user and give USER_ADMIN System Privilege to both AAA and ZZZ users.

Steps if the SQL is run by SYSTEM user:

a. Login as AAA and open user SYSTEM.

b. Add AAA schema under SQL privileges and give SELECT access

c. Login as ZZZ and open user SYSTEM

c. Add ZZZ schema under SQL privileges and give INSERT access

Steps if the SQL is run by AAA user (it already has SELECT access on the table within):

a. Login as ZZZ and open user AAA

b. Add ZZZ schema under SQL privileges and give INSERT access

Steps if the SQL is run by ZZZ user (it already has INSERT access on the table within):

a. Login as AAA and open user ZZZ

b. Add AAA schema under SQL privileges and give SELECT access

Thanks,

Anooj

[a_ahmad · Accepted solution]

Hello Anooj,

Thanks. It works perfect.

one clarification

for user AAA(it already has SELECT access on the table within)

for user ZZZ(it already has INSERT access on the table within)

I've understood the concept here, would you mind clearing me how User ZZZ or AAA can be granted/revoked respective privileges.

Thanks again for clear explanation

Ahmad

[Former Member · Accepted solution]

Ahmad,

Basically only the owner of a particular schema can gives privileges to that schema to other users. So for e.g. if AAA wants insert access in ZZZ, you have to login as the owner of ZZZ user, open AAA schema owner/user, add ZZZ schema and give necessary privileges.

However, you can also give "grantable to others" privilege to other users. For e.g. in the above case, instead of giving access straight to AAA user, you could give it to SYSTEM user for instance and also select "grantable to others". That way SYSTEM will also be abto give privileges to ZZZ schema to any other users.

Hope that is clear.

Thanks,

Anooj

[hai_murali_here]

Hi Ahmad,

If you have both Schemas in the same HANA System,then you can go with Anooj approach.

INSERT INTO "ZZZ"."table1" SELECT * FROM "AAA"."table1";

But if you have both schemas in different HANA Systems,then you have to go for export and import

1.Export the tables to one HANA System

2.Copy it from there to target HANA System

3.Import from the Target HANA System

Rgds,

Murali

[a_ahmad]

Hi Rama and Murali,

I tried Import/Exp. First of all I need to get authorizations to do this. anyways

with Export come two options:

- Export tables on server

then it written: If you specify a path, the directories must already exist

How to create these directories??

- export tables to current client

if this is done then can import which like 'importing .csv file' scenario (the way it is implemented on SAP provided dev. system)

I am implementing test scenario on our own HANA SPS03 system.

How to create directories on HANA server? do I need to contact sys. admin? its better if I learn to handle on my own?

Is is then again using FTP client to put files on dirs in HANA server?

what other authorizations do I need?

Thanks

[hai_murali_here]

Hi Ahmad,

You need EXPORT and IMPORT "System Privileges" to perform this Export/Import through your user.

-Export tables to Server, exports to default location which is "/usr/sap/<HANADB>/HDB01/work".You can also change this default location

-Export tables to current client,exports the tables to local client location,but you cant import these tables since there are no such option(Importing tables from local client using Wizard)

You can use Flat file upload directly,if you have your data in excel or csv files.(Refer:http://scn.sap.com/docs/DOC-27960)

For Export/IMport,Refer http://scn.sap.com/docs/DOC-26381

You can also create directories in HANA Server as its just a linux system

As I mentioned earlier,

if you want to move tables between the schemas in same HANA System,you can simply use SQL Scripts mentioned above.

if you want to move tables between the schemas of different HANA Systems,then go for Export/Import

Hope this helps.

Rgds,

Murali

[hai_murali_here]

Hi Ahsan,

Get the required privileges from your HANA System Admin as the HANA Server is maintained seperately

You no need to access the HANA Server directory at all,if you want to load data to HANA DB from local file(csv or xls).

Keeping the local file(which has data) in the server directory and doing import through SQL is old approach.Now with new revisions(Rev 29),direct upload is available.

Rgds,

Murali

[a_ahmad]

Hi Murali,

First with Anooj solution INSERT INTO "ZZZ"."table1" SELECT * FROM "AAA"."table1";

i am getting msg: SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

any idea what authorization still necessary?

Secondly, I got Import/Export authorization but when I try to export a table and give Target server location: /usr/sap/<HANADB>/HDB01/work , i get msg: Cannot open path

our HANA system is installed remotely and i have access through Studio. How can I access Server to create directories there and store data on Server?

Export to client location worked fine. we are still on SPS03, therefore have to use old way.

As we learned by imp. scenario on SAP dev. center HANA system, if FTP client is used to access Server then have to exe. SELECT * FROM SYSTEM.FTP_SERVER to get needed info, but this is not working for me on our HANA system. How can I resolve this approach?

Best regards

Ahmad

[rama_shankar3]

Ahmad:

You can also use the standard feature ("import /export ") available part of HANA studio to copy data across tables in difference schema.

Refer to step-by-step mentioned in :

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

Regards,

Rama