Generate insert column based on select columns

Question

I have a scenario, where 100's of select statements sql's are in one metadata table or some text file.

Need to insert all sql results into one specific table. (master table has col1, col2,col3 .... 200columns )

problem im facing(ORA-00947) is every select statement has different number of columns. .. i need to generate INSERT PART.

CASE 1 : INSERT INTO (COL1,COL2,COL3) <<this select part comes from file/variable>>

CASE 2 : INSERT INTO (COL1) <<this select part comes from file/variable>>

CASE 3 : INSERT INTO (COL1) <<this select part comes from file/variable>>

have to figure out how many columns are in select part then generate INSERT part. .

Thought of create as select but problem is some select statement has max(col) without alias so it will fail.

Dynamic queries might be a good place to start. If this was SQL Server, I'd be able to answer, but I'm not as familiar with Oracle. Link to the Oracle docs is here: docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg09dyn.htm — Mark Moretto
This is not a technical problem, it is a political problem. You need to push back on the requirements. Nobody cannot write a robust solution to process the <<this select part comes from file/variable>> unless it comes with the correct metadata for each select so we can assemble the dynamic SQL correctly. — APC
Thanks Laks. How would one know what column of the master table should receive max(col) if there is no matched column alias? Does the master table have some reliable way to translate, for example max(col) => MAX_COL or something? — alexgibbs
Master table don't have any matching name, it just has col1,col2.....etc. I no need to worry abt what data comes in. I just need to insert in the same order. — Laks

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-10-05T13:39:48

This is too long for a comment.

If you are storing SQL in a table, then you are constructing your query dynamically. So, update the table and list the columns that you want.

You could then construct the inserts as :

insert into master_table (<column list here>)
    <select here>;

Both the select and column list would come from the table.

Generate insert column based on select columns

2 Answers