CODEWITHSUNDEEP
sqloracle-databaseselectsql-insert
Etsitpab Nioliv
Etsitpab Nioliv

Reputation: 424

Use SELECT results as column name for parent SELECT

I have a TMP_FOO table that has the same columns as FOO plus some more. I need to insert all the rows from TMP_FOO into FOO. I could write the following:

INSERT INTO FOO (SELECT bar, baz FROM TMP_FOO);

But I need it to be generic so that I only change the name of the tables. My best try:

INSERT INTO FOO
(SELECT 
  (SELECT column_name
  FROM USER_TAB_COLUMNS
  WHERE table_name = 'FOO')
FROM
 f2020.TMP_FOO)

Which doesn't work because 

SELECT column_name
FROM USER_TAB_COLUMNS
WHERE table_name = 'FOO'

returns multiple rows. Can I archieve what I want in plain SQL (without needing to dynamically build the query string)?

Upvotes: 0

Views: 199

Answers (2)

Pavel Musil
Pavel Musil

Reputation: 494

You don't use columns names from the result, INSERT INTO table SELECT must keep same count of columns with destination table. I think, that is not possible create column list runtime. You must type all columns of FOO - as your first example:

INSERT INTO FOO (SELECT FOO_colname_1, FOO_colname_2, FOO_colname_3 FROM TMP_FOO);

and reverse, if FOO_TEMP has more columns you must add NULL or default values:

INSERT INTO TMP_FOO (SELECT TMP_FOO_colname_1, TMP_FOO_colname_2, TMP_FOO_colname_3, NULL, NULL FROM FOO);

Upvotes: 0

GMB
GMB

Reputation: 222632

Can I archieve what I want in plain SQL (without needing to dynamically build the query string)?

No.

In plain SQL, you cannot generate the list of output column from another query. The database needs to know which columns the query will return at the time when it parses the query (ie before the query is actually executed).

Upvotes: 2

Related Questions