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?

Answer 1

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.

Answer 2

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.