SQL query with dynamic columns and dynamic data

Question

My query.

INSERT INTO TARGET_TABLE (SELECT DATA FROM TABLE_DATA WHERE TYPE = 'HEADER') VALUES
(SELECT DATA FROM TABLE_DATA WHERE TYPE = 'ITEMS')

In this query I am trying to insert data into TARGET_TABLE. TABLE_DATA will have data in the below format

SELECT DATA FROM TABLE_DATA WHERE TYPE = 'HEADER'
Result COUNTRY,ID,NAME

SELECT DATA FROM TABLE_DATA WHERE TYPE = 'ITEMS'
Result 'IN','123','xyz'

So I am expecting the below

INSERT INTO TARGET_TABLE (COUNTRY,ID,NAME) VALUES ('IN','123','xyz')

Any thoughts on this?

Any Inputs / Suggestions on this topic?

Madhu
– Madhu

2019-10-04 11:46:44 +00:00
Commented Oct 4, 2019 at 11:46 — Madhu
– Madhu, Commented Oct 4, 2019 at 11:46

Ed Bangga · Accepted Answer · 2019-10-04 05:15:57Z

1

select case is what you need

INSERT INTO TARGET_TABLE 
(SELECT case when type='HEADER' then COL1 else 'aa' END
     ,case when type='HEADER' then COL2 else 'bb' END
     ,case when type='HEADER' then COL3 else 'cc' END
     ,case when type='HEADER' then COL4 else 'dd' END
FROM TABLE_DATA )

answered Oct 4, 2019 at 5:15

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Madhu Over a year ago

Nope, I am trying to achieve dynamic data, but in your case you are hard coding the columns

Popeye · Accepted Answer · 2019-10-04 05:19:14Z

1

You need to use a dynamic query like the following:

BEGIN
EXECUTE IMMEDIATE 
  'INSERT INTO TARGET_TABLE ( ' 
  || SELECT DATA FROM TABLE_DATA WHERE TYPE = 'HEADER' 
  || ') VALUES ('
  || SELECT DATA FROM TABLE_DATA WHERE TYPE = 'ITEMS' 
  || ')';
END;
/

Cheers!!

answered Oct 4, 2019 at 5:19

Popeye

36k4 gold badges12 silver badges31 bronze badges

7 Comments

Madhu Over a year ago

Thanks for your response, However when I use the below <pre><code> INSERT INTO TARGET (SELECT REPLACE( RAW_DATA,' ',',') FROM TABLE_DATA WHERE ROW_NO = 1) VALUES (SELECT ''''|| REPLACE( RAW_DATA,' ',''',''') ||'''' FROM TABLE_DATA WHERE ROW_NO <> 1) </code></pre> Its giving error missing SELECT keyword.

Popeye Over a year ago

I am not sure which platform you are executing the query. If you execute the code from my answer on SQL*Plus or SQL Developer then you will be able to achieve the desired result and then you need to convert the code to what is supported in your platform.

Madhu Over a year ago

I am executing in SQL Developer

Popeye Over a year ago

What is the result of the

DBMS_OUTPUT.PUT_LINE('INSERT INTO TARGET_TABLE ( '    || SELECT DATA FROM TABLE_DATA WHERE TYPE = 'HEADER'    || ') VALUES ('   || SELECT DATA FROM TABLE_DATA WHERE TYPE = 'ITEMS'    || ')');

Madhu Over a year ago

Its just as the below 'INSERT INTO TARGET_TABLE ( ' || SELECT DATA FROM TABLE_DATA WHERE TYPE = 'HEADER' || ') VALUES (' || SELECT DATA FROM TABLE_DATA WHERE TYPE = 'ITEMS' || ')'

|

Collectives™ on Stack Overflow

SQL query with dynamic columns and dynamic data

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related