How to INSERT INTO table from dynamic query?

Question

My version of Postgres is:

"PostgreSQL 9.4.4, compiled by Visual C++ build 1800, 32-bit"

Let's say I have two tables Table1 and Table2, which are having column col1 and col2 respectively.

CREATE TABLE Table1(col1 int);
CREATE TABLE Table2(col2 int);

There is another table Table3 storing a formula for migrating data from Table1 to Table2:

CREATE TABLE Table3 (     
  tbl_src   varchar(200),
  col_src   varchar(500),
  tbl_des   varchar(200),
  col_des   varchar(100),
  condition varchar(500)
);

INSERT INTO Table3 (tbl_src, col_src, tbl_des, col_des, condition)
SELECT 'Table1','col1','Table2','col2',NULL

How to compile this formula in a dynamic query and insert into the destination table?

@ErwinBrandstetter updated the version in post. And i don't want to create 100 rows in destination table. Table1 is source and Table2 is my desination.We can ignore the condition column.how their columns are linked/mapped is stored in Table3. Challenge is to make this into dynamic it into dynamic sql and insert it into destination table. Sir my Question is some linked to post at [link](http://stackoverflow.com/questions/34133244/how-to-get-result-from-dynamic-sql-in-postgres) and at [link](http://stackoverflow.com/questions/34172019/insert-from-dynamic-query-in-postgres) — Harsimranjeet Singh, Dec 10 '15 at 08:52
@ErwinBrandstetter Sir i was having a wish that you can see my problem as i've noticed that you have resloved lot of postgres problems. Thanks for making wish real. — Harsimranjeet Singh, Dec 10 '15 at 08:55
I rolled back your last edits and suggest you start a new question for the new question. You can find your version in the [edit history here](http://stackoverflow.com/posts/34194889/revisions). — Erwin Brandstetter, Dec 10 '15 at 10:19

score 1 · Accepted Answer · edited May 23 '17 at 11:44

1

The basic query to build the command dynamically:

SELECT format('INSERT INTO %I (%I) SELECT %I FROM %I'
            , tbl_des, col_des, col_src, tbl_src) As sql
FROM   table3;

This produces a query like:

INSERT INTO "Table2" (col2) SELECT col1 FROM "Table1"

Note the quoted upper-case spelling. Unlike in SQL commands, where unquoted identifiers are folded to lower-case automatically, the strings in your table are now case-sensitive!

Are PostgreSQL column names case-sensitive?

I suggest you never double-quote identifiers and use legal, lower-case names exclusively.

To automate:

DO
$$BEGIN
   EXECUTE (
      SELECT format('INSERT INTO %I (%I) SELECT %I FROM %I'
                  , tbl_des, col_des, col_src, tbl_src) As sql
      FROM   table3
      -- WHERE table3_id = 123  -- select only *one* row!
      );
END$$;

You need to understand the format() function. Read the manual.

You can wrap this into a plpgsql function as well and pass additional parameters:

Table name as a PostgreSQL function parameter

edited May 23 '17 at 11:44

Community

1
1

answered Dec 10 '15 at 09:53

Erwin Brandstetter

605,456
145
1,078
1,228

Sir this resolved completely one to one mapping. But what if there are three columns of table1 and mapped to three columns in table2. I've modified example for same in post. – Harsimranjeet Singh Dec 10 '15 at 10:14
@HarsimranjeetSingh: Please don't change the nature of the question after an answer has been given. I tried to get clarification before I answered - and clarification is always welcome, but changing the nature of the question is not. Start a new question for the new question - you can always link to this one for context. – Erwin Brandstetter Dec 10 '15 at 10:18
Sir i've posted the same at [link](http://stackoverflow.com/questions/34199385/how-to-insert-into-table-having-multiple-column-from-dynamic-query) – Harsimranjeet Singh Dec 10 '15 at 10:27
One this more Sir, I tried to run this automate part at query window, which return : ERROR: more than one row returned by a subquery used as an expression CONTEXT: SQL statement "SELECT ( SELECT format('INSERT INTO %I (%I) SELECT %I FROM %I' , tbl_des, col_des, col_src, tbl_src) As sql FROM md_formula -- WHERE table3_id = 123 -- select only *one* row! )" PL/pgSQL function inline_code_block line 2 at EXECUTE statement – Harsimranjeet Singh Dec 10 '15 at 10:34
Do we always need to wrap this kind of code in plpgsql, Can't we execute directly? – Harsimranjeet Singh Dec 10 '15 at 10:35
@HarsimranjeetSingh: "more than one row": you did see my comment above saying `select only *one* row!`? "Always plpgsql?" Effectively, yes. `EXECUTE` is a plpgsql command, not an SQL command. The default procedural language in a `DO` command is also plpgsql. You can so similar things with other script languages or other server-side procedural languages, but you cannot do the same with just SQL. [Find some related questions.](http://stackoverflow.com/search?q=[postgresql]+[plpgsql]+DO+command+function+code%3AEXECUTE) – Erwin Brandstetter Dec 10 '15 at 10:50

How to INSERT INTO table from dynamic query?

1 Answers1

Linked