Requirement –
One of most common requirement in Hana is to move user defined table from one Hana environment to another (Ex: Dev > QA > Prod). Here is one of the method i.e. Export & Import we can use.
Solution –
Will migrate the Hana Table from Source Hana Environment to Target Hana Environment.
Step 1 Export Table - First we have to export the table from source system.
Connect to source Hana system and create sample table and load data using below script or any other script.
CREATE COLUMN TABLE XX.IMPORT_EXPORT (ID INTEGER, NAME NVARCHAR (100), AMOUNT DECIMAL (38, 18));
DO BEGIN
DECLARE v_NEW_ID INTEGER;
DECLARE v_COUNTER INTEGER;
DECLARE v_NAME NVARCHAR (100);
v_COUNTER = 1;
WHILE v_COUNTER <= 100000
DO
INSERT INTO XX.IMPORT_EXPORT VALUES (:v_COUNTER, 'A', 100);
v_COUNTER = :v_COUNTER + 1;
END WHILE;
END;
Click on File menu in Hana Studio -> Select Export Option.
Export screen will appear. Type Catalog in the search box and Catalog Object will come on the screen. Select this option and hit Next
Select Source Hana Database on next screen and click next
.
Now, select which table needs to be exported.
On next screen we will see additional configuration for the Export.
- Column Table Format - We can choose the Export format as Binary or CSV.
- Including Data - Checking this checkbox will Export table with data.
- Including Dependencies - Checking this checkbox will Export table with it's dependent objects like Triggers.
- Export Location - We can save the exported table to Server or to Local System.
- Export Options - We can define the Number for Parallel Threads can be used during export.
After clicking finish, table will get exported to provided location and create folders like below.
Export of table completed now.
Step 2 Import Table - As table is exported now we can import the table into target Hana environment using below steps.
Connect to Target Hana system. Click on File menu in Hana Studio -> Select Import Option.
Import screen will appear. Type Catalog in the search box and Catalog Object will come on the screen. Select this option and hit Next
Select Target Hana Database on next screen and click next.
Note - Here, I have used same System as Target and Source for testing purpose. Before Importing, I have dropped the table from database.
On next screen we have to choose location at which we exported the table in Step 1.
Next screen will show the exported table name.
On next screen we will see additional configuration for the Import.
- Including Data - Checking this checkbox will Import table with data.
- Including Dependencies - Checking this checkbox will Import table with it's dependent objects like Triggers.
- Import Options - We can define the Number for Parallel Threads can be used during Import.
- Replace Existing Catalog Objects - This option will overwrite the existing objects during import.
After clicking finish table will import in the target system.
Thanks & Happy Learning!