How can I use data from 1 table and then INSERT into a second using data as a column header SQL?

Question

I am trying to build queries to take data from table_1:

And use it as the value AND column header for table_2 in SQL.

Please note, table_2 is already created and has the correct column names. It is just empty to begin and the result is the following.

id	name	address	state
1	Company ABC	null	null
2	null	123 Main St	null
3	Company 123	null	null

I understand this may look silly, but there's a reason for this, as it is just a piece of the puzzle I am trying to solve.

I have tried something like this:

WITH dynamic_columns AS ( SELECT path, curr_value FROM table_1 )

and

SELECT CONCAT('INSERT INTO \'', path, '\' VALUES (\'', curr_value, '\')') FROM dynamic_columns

I get the following:

INSERT INTO 'name' VALUES 'COMPANY ABC' INSERT INTO 'address' VALUES '123 Main St' INSERT INTO 'name' VALUES 'COMPANY 123'

I know this isn't correct, but how do I fix it and actually execute?

If you remove those extra quotes (like ('INSERT INTO ', path, ' VALUES...)), you'll build proper SQL, but how are you going to execute it? I think you're going to need a programming language. — Tim Roberts
– Tim Roberts, Commented Apr 11 at 18:21
I created this fiddle to help us collaborate on a solution dbfiddle.uk/lAMZ1skl — Bart McEndree
– Bart McEndree, Commented Apr 11 at 18:22
Use double quotes for identifiers. Do "name" for a table. ('name' is a string literal.) — jarlh
– jarlh, Commented Apr 11 at 18:23
Here is how you populate table2 using INSERT INTO dbfiddle.uk/SNrzc7T5 — Bart McEndree
– Bart McEndree, Commented Apr 11 at 18:31
What RDBMS are you using? And just work backwards, work out what the static SQL should be, then build a query to create that (using the correct delimiting for columns and strings as already mentioned). — Dale K
– Dale K, Commented Apr 11 at 22:26

Diyor Khakimov · Accepted Answer · 2025-04-13 08:30:27Z

Try this

SELECT CONCAT( 'INSERT INTO table_2 (id, ', path, ') VALUES (', id, ', ''' || curr_value || ''');' ) AS generated_sql FROM table_1;

This returns a list of INSERT statements you want — which you can then copy and run them manually or via script.

Using standard || is better, I followed the author's code to make it easier for him to understand

1 Answer 1