Implementing SQL Transform

• SQL transformation is used to perform standard SQL Operations in Data services but it is not supports all SQL operations.
• It supports only retrieval operations (select statements).Using SQL transformation we can’t manipulates (Insert, Delete, Update) the data.
• SQL transformation and lookup function performs same operations. Performance of SQL transformation is very good compared to lookup function.

Steps:-

• Right click on Project -> New -> Data Flow.

• Provide the name and Description.
• Click on Finish.

• Drag and drop SQL Transform onto Data Flow editor.

• Drag and drop Query transform onto the Data Flow editor.

• Drag and drop Template Table onto the Data Flow editor.

• Select MSSQL data store and click on OK.

• Provide Table Owner and Table name.
• Click on OK.

• Rename SQL Transform. 

• Click on show values at Data Store .
• select HANA data store and click on OK.

• Go to HANA studio.
• System -> Content -> Package -> Sub package -> Calculation views -> Right click Calculation view.

• Click on data preview.

• Click on raw data.

• Click on show log.

• Double click on generated SQL.

• Copy the SQL statement.

• Now, paste the SQL code in the space as shown below .

• Click on validate icon.

• Pop-up will appear " Validation successful ".

• Click on detect schema.

• On clicking on detect schema, we can see fields in the output schema.

• Now, link read from HANA to query and from query to Target Table.

• Click on Query transform.

• Drag and drop BUKRS, BUTXT, KUNNR, NAME1, MAKTX, YEAR, VV010, VV010001 these objects onto query transform .

• Click on target table and click on columns.

• On clicking the columns, we can see the output of the Template table.

• Click on validation.

• Pop-up will appear " Validation successful".
• Click on close.

• Click on execute.

• Execution success.

• Logon to MSSQL: databases -> Tables -> find Template table name.

• Right click on object and click on select top 1000 rows.

• Now, we can see data in MSSQL.