Insert into multiple tables form one query

Question

I am retrieving the SUM of some data from a query. The SUM can have both negative and positive values. I want to insert the positive values to one table and the negative values to another table.

The result set for the select query is like below

total       |   userid
-----------------------
4750.00     |   11
1339.00     |   3607
-681.81     |   3600

I was planning to insert the details from the select query directly to the table, INSERT into table (amount,user) SELECT SUM(..) AS total,userid FROM... . But I was not able to figure out how to do it in a single query.

I am using PostgreSQL 8.4.17

There is no issue in using two queries. But still i don't get how to add an if/else statement in between the INSERT and SELECTquery — Nandakumar V
– Nandakumar V, Commented Jun 10, 2013 at 10:55

Gordon Linoff · Accepted Answer · 2013-06-10 10:57:20Z

2

Use the where statement in two separate inserts:

INSERT into positives (amount,user)
    SELECT SUM(..) AS total, userid
    FROM...
    having sum(..) > 0;

INSERT into negatives (amount,user)
    SELECT SUM(..) AS total, userid
    FROM...
    having sum(..) < 0;

answered Jun 10, 2013 at 10:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Nandakumar V Over a year ago

The equation inside the sum is a complex one SUM( ((a*b)/100) - (c/100) ). So calculating it two times can be a bit heavy ... right?

Gordon Linoff Over a year ago

@NandakumarV . . . That is not really that complicated. The bigger performance hit is reading the table twice. If the query really takes a noticeable amount of time to run, then put the full results in a temporary table and just insert from that.

Collectives™ on Stack Overflow

Insert into multiple tables form one query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related