SQL procedure for dynamic tables

Question

I need to create a dynamic procdedure where I must compare the columns of two tables. The end result SQL looks like this

SELECT "ID", "FIELD_1", "FIELD_2", "FIELD_3","FIELD_4","FIELD_5" 
FROM (

SELECT "ID", "FIELD_1", "FIELD_2", "FIELD_3", "FIELD_4", "FIELD_5"

FROM TABLE1

MINUS

SELECT "ID", "FIELD_1", "FIELD_2", "FIELD_3", "FIELD_4", "FIELD_5"

FROM TABLE2 )

 

UNION ALL

SELECT "ID", "FIELD_1", "FIELD_2", "FIELD_3", "FIELD_4", "FIELD_5" 

FROM (

SELECT "ID", "FIELD_1", "FIELD_2", "FIELD_3", "FIELD_4", "FIELD_5"

FROM TABLE2

MINUS

SELECT "ID", "FIELD_1", "FIELD_2", "FIELD_3", "FIELD_4", "FIELD_5"

FROM TABLE1 );

I'm using this query to get the column from all tables:

SELECT * FROM TABLE_COLUMNS 
WHERE SCHEMA_NAME ='MY_SCHEMA' AND TABLE_NAME='TABLE1' ORDER BY POSITION;

In this code the number of fields in each table is six, but this will vary as per tables.

If I were to do this in any other language, I'll write a function which takes the table names in argument get the column names and save them in an array and iterate through them and concatenate to the query. I'm new to SQL so I don't know if its even possible to do this. Is there a way to implement this?

If you were to ask this question in any other language, you'd probably specify what language you're on. An answer in JavaScript might not be as useful to someone using Java. You should therefore tag your question with the relevant RDBMS, SQL Server will have a significantly different answer than Oracle for example. — MarcinJ, May 22 '23 at 10:39
My bad, I have to do this in SAP HANA SQL. Is it different from SQL Server? — TrenT, May 22 '23 at 11:04
We know for sure the query in the question is not written for SQL Server, PostgreSQL or SQLite, because those RDBMS use EXCEPT, not MINUS operator. So best guess is: MySQL or Oracle. But indeed, please provide a database system tag to better tune the answer. — alejandroMAD, May 22 '23 at 11:06
Then it would seem [SYS.COLUMNS](https://answers.sap.com/questions/10126431/how-to-get-all-column-names-from-a-table-in-sap-ha.html) would be the answer to your needs in SAP HANA SQL, TrenT. — alejandroMAD, May 22 '23 at 11:13
I'm sorry for the confusion, I've edited the question. I run this on HANA database .Is this okay now? — TrenT, May 22 '23 at 11:28

alejandroMAD · Answer 1 · 2023-05-22T11:07:52.823

There are several ways to achieve this in SQL, depending on the specific database management system you're using and your database and tables characteristics. You can use metadata tables and system views to retrieve a table's column names and then construct a dynamic query.

Here's a way you can do this in MySQL implementation, for example, as you didn't provide information on the database management system in your question:

SET @table1_columns = (
    SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') SEPARATOR ', ')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TABLE1'
);

SET @table2_columns = (
    SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') SEPARATOR ', ')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TABLE2'
);

SET @query = CONCAT('
SELECT `ID`, ', @table1_columns, '
FROM (
    SELECT `ID`, ', @table1_columns, '
    FROM TABLE1
    MINUS
    SELECT `ID`, ', @table2_columns, '
    FROM TABLE2
)
UNION ALL
SELECT `ID`, ', @table2_columns, '
FROM (
    SELECT `ID`, ', @table2_columns, '
    FROM TABLE2
    MINUS
    SELECT `ID`, ', @table1_columns, '
    FROM TABLE1
)');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

We leverage the GROUP_CONCAT aggregate function to concatenate column names into a comma-separated list. The INFORMATION_SCHEMA.COLUMNS table is used to retrieve the column names for the tables TABLE1 and TABLE2.

The CONCAT string function allows to add backticks around each column name to handle any special characters or reserved words which might be included in column names.

Then we dynamically insert the column names retrieved into the query string using a string concatenation.

I'm assuming that the column names in both tables in your case are the same and in the same order. If these names differ or the order is not consistent, you may need some additional logic within that dynamic query to handle the mapping of columns between the two tables.

You should also check out this post for more knowledge.

It is VARCHAR in my example on MySQL and it'd be the same for analogous operation in SAP HANA SQL, because the variable keeps not other thing than a concatenation String of column names. — alejandroMAD, May 23 '23 at 07:58

score 0 · Answer 2 · answered May 22 '23 at 13:31

You may want to take a look at Dynamic SQL for SAP HANA. You can use EXEC or EXECUTE IMMEDIATE within a Stored Procedure to execute a string as SQL Command. As you suggested, you need to assemble the statement with the column information retrieved from TABLE_COLUMNS.

SQL procedure for dynamic tables

2 Answers2