12 Replies Latest reply: May 24, 2012 7:59 AM by Anooj Behanan RSS

insert data from one Schema table to another Schema table

A Ahmad
Currently Being Moderated

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

  • Re: insert data from one Schema table to another Schema table
    Anooj Behanan
    Currently Being Moderated

    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

    • Re: insert data from one Schema table to another Schema table
      A Ahmad
      Currently Being Moderated

      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

      • Re: insert data from one Schema table to another Schema table
        Anooj Behanan
        Currently Being Moderated

        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

        • Re: insert data from one Schema table to another Schema table
          Anooj Behanan
          Currently Being Moderated

          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

          • Re: insert data from one Schema table to another Schema table
            A Ahmad
            Currently Being Moderated

            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

            • Re: insert data from one Schema table to another Schema table
              Anooj Behanan
              Currently Being Moderated

              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

  • Re: insert data from one Schema table to another Schema table
    Rama Shankar
    Currently Being Moderated

    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

  • Re: insert data from one Schema table to another Schema table
    Muralikrishnan E
    Currently Being Moderated

    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

    • Re: insert data from one Schema table to another Schema table
      A Ahmad
      Currently Being Moderated

      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

      • Re: insert data from one Schema table to another Schema table
        Muralikrishnan E
        Currently Being Moderated

        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

        • Re: insert data from one Schema table to another Schema table
          Muralikrishnan E
          Currently Being Moderated

          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

          • Re: insert data from one Schema table to another Schema table
            A Ahmad
            Currently Being Moderated

            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

Actions