HANA Stored Procedure - Dynamically assign column name while calling a select using a variable

[Satheesh9204]

Hallo Experts,

I have a control table with below columns

RowID||Table Name||Field||Description

1||TVKO||VKORG||Sales Org

2||TVKOV||VTWEG||Dist Channel

I need to write a Stored procedure to read data from TVKO and TVKOV tables from VKORG and VTWEG fields and insert to a table named Merge_Table.

Initially I am storing TVKO, VKORG and Sales Org Values in variables and calling a insert function as below

insert into "DBADMIN"."Merge_Table" ("Check_Table","Code", "Description") 
          select v_check_table, :v_code, v_description from "DBADMIN"."v_check_table"

In above query for 2nd column I need values stored in VKORG field, howevever instead of values VKORG is getting inserted. 

Requesting help to modify above query where data from column is read dynamically.

1. Thank You 

[Vitaliy-R · Accepted solution]

Interesting question @Satheesh9204!

I had to use Dynamic SQL in my approach, although I do not know if this is the most optimal and/or elegant solution.

I simulated your environment using two system tables to read data from `TABLES` and `VIEWS` tables from `TABLE_NAME` and `VIEW_NAME` fields and insert them into a table named `Merge_Table`.

Here is my procedure:

DO BEGIN
    DECLARE CURSOR c_cursor1 FOR
        SELECT
        	"Table_Name",
        	"Field",
        	"Description"
        FROM "v_check_table"
        ORDER BY "RowID";

    TRUNCATE TABLE "Merge_Table";

    FOR cur_row AS c_cursor1
	DO
        EXEC 'INSERT INTO "Merge_Table" ( SELECT '''|| :cur_row."Table_Name" || ''' AS "Field", ' || :cur_row."Field" || ' AS "Code", '''|| :cur_row."Description" || ''' AS "Description" FROM ' || :cur_row."Table_Name" ||' )';
    END FOR;
    CLOSE c_cursor1;
    
    SELECT "Check_Table", count(*) FROM "Merge_Table" GROUP BY "Check_Table";
END;

And the output of its execution:

I hope this helps as a starting point for you,
--Vitaliy 🇺🇦

[Vitaliy-R · Accepted solution]

And my complete code to simulate the question:

DROP SCHEMA Vital_Sandbox CASCADE;
CREATE SCHEMA Vital_Sandbox;
SET SCHEMA Vital_Sandbox;

DROP TABLE "v_check_table";
CREATE TABLE "v_check_table" AS (
    SELECT 1 AS "RowID", 'TABLES' AS "Table_Name", 'TABLE_NAME' AS "Field", 'System table for Tables' AS "Description" FROM DUMMY
    UNION ALL
    SELECT 2 AS "RowID", 'VIEWS' AS "Table_Name", 'VIEW_NAME' AS "Field", 'System table for Views' AS "Description" FROM DUMMY
);

DROP TABLE "Merge_Table";
CREATE TABLE "Merge_Table" AS (
    SELECT T1."Table_Name" as "Check_Table", T2."TABLE_NAME" AS "Code", T1."Description" AS "Description" 
    FROM "v_check_table" T1 CROSS JOIN "TABLES" T2
    WHERE 1<>1 
);

DO BEGIN
    DECLARE CURSOR c_cursor1 FOR
        SELECT
        	"Table_Name",
        	"Field",
        	"Description"
        FROM "v_check_table"
        ORDER BY "RowID";

    TRUNCATE TABLE "Merge_Table";

    FOR cur_row AS c_cursor1
	DO
        EXEC 'INSERT INTO "Merge_Table" ( SELECT '''|| :cur_row."Table_Name" || ''' AS "Field", ' || :cur_row."Field" || ' AS "Code", '''|| :cur_row."Description" || ''' AS "Description" FROM ' || :cur_row."Table_Name" ||' )';
    END FOR;
    CLOSE c_cursor1;
    
    SELECT "Check_Table", count(*) FROM "Merge_Table" GROUP BY "Check_Table";
END;

[Satheesh9204 · Accepted solution]

Thank You for the solution. This worked as expected.