Issue in copying rows from one table to another

Question

I am implementing a request mechanism where the user have to approve a request. For that i have implemented a temporary table and main table. Initially when the request is added the data will be inserted to the temporary table, on approval it will be copied to the main table.

The issue is there will be more than 5k rows to be moved to the main table after approval + another 3-5 row for each row in the detail table (stores the details). My current implementation is like this

//Get the rows from temporary table (batch_temp)
    //Loop through the data
        //Insert the data to the main table (batch_main) and return the id 
        //Get the details row from the temporary detail table (batch_temp_detail) using detail_tempid
            //Loop through the data
                //Insert the details to the detail table (batch_main_detail) with the main table id amount_id
            //End Loop  
    //End Loop

But this implementation would take atleast 20k queries. Is there any better ways to implement the same.

I tried to create a sqlfiddle but was unable to create one. So i have pasted the query in pgsql.privatepaste.com

Answer 1

I'm sorry that I'm not familiar with PostgreSQL. My solution is in MySQL, I hope it can help since if they (MySQL & PostgreSQL) are same.

First, we should add 1 more field into your batch_main table to track the origin batch_temp record for each batch_main record.

ALTER TABLE `batch_main`
    ADD COLUMN tempid bigint;

Then, on approval, we will insert 5k rows by 1 query:

INSERT INTO batch_main
    (batchid, userid, amount, tempid)
    SELECT batchid, userid, amount, amount_id FROM batch_temp;

So, with each new batch_main record we have its origin batch_temp record's id. Then, insert the detail records

INSERT INTO `batch_main_detail`
    (detail_amount, detail_mainid)
    SELECT
        btd.detail_amount, bm.amount_id
        FROM 
            batch_temp_detail `btd`
            INNER JOIN batch_main `bm` ON btd.detail_tempid = bm.tempid

Done!

P/S: I'm confuse a bit about the way you name your fields, and since I do not know about PostgreSQL and by looking into your syntax, can you use same sequence for primary key of both table batch_temp & batch_main? If you can, it's no need to add 1 more field.

Hope this help,