Efficient method to Copy Data from one table to the other table in the same database

Question

I am looking for a more efficient way to transfer data from one table to the other table. Assuming I have 2 tables, STUDENT and STUD and I have to take into consideration that there will be millions of transactions being made each day.

I am using PGAdmin. This is an example of my current script to transfer data from STUD to STUDENT table:

CREATE TABLE STUDENT(
STUDENT_NAME TEXT,
STUDENT_CLASS TEXT,
STUDENT_NO INTEGER,
STUDENT_ADDRESS TEXT,
.
.
.
);  

INSERT INTO STUDENT(
STUDENT_NAME,
STUDENT_CLASS,
STUDENT_NO,
STUDENT_ADDRESS
)
SELECT
STUD_NAME,
STUD_CLASS,
STUD_NUM,
STUD_ADDS  

FROM STUD

Thanks

Not a big deal if you have suitable hardware. What exactly is your question anyway? — e4c5, Mar 08 '17 at 03:55
I am looking for another way to script it so that the code will be efficient when there are hundreds of columns and millions of transactions. — user7609786, Mar 08 '17 at 03:59
Well there shouldn't be hundreds of columns in a student table in the first place. Like is said, please explain what you are trying to do http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem — e4c5, Mar 08 '17 at 04:00
@e4c5 This table is just an assumption that it is Student table ("Assuming I have 2 tables, STUDENT and STUD"). The transaction table I have has hundreds of column x.x — user7609786, Mar 08 '17 at 04:08
What about [this](http://stackoverflow.com/questions/758945/whats-the-fastest-way-to-do-a-bulk-insert-into-postgres) answer? Is it applicable to you? — cha, Mar 08 '17 at 04:09
Are you trying to run that query each time a new item get's into your main table. As I have repeatedly asked, __please clarify your question__ — e4c5, Mar 08 '17 at 04:12
@e4c5 The query will be running every 30 minutes and there will be new item in the main table. — user7609786, Mar 08 '17 at 04:17
If you are looking for data backup check for the **Replication**. — Rakesh, Mar 08 '17 at 05:43

score 1 · Accepted Answer · answered Mar 08 '17 at 04:57

Why Insert?

First of all, bulk inserting all data from one table to another every half and hour is going to make it very hard on your database if you have millions of rows. The site/app will become unresponsive during the time that insert is happening.

Do you really need to copy? One of the fundamental principals of RDBMS is to avoid redundancy. Saving a subset of data in another table is highly redundant to say the least.

Solution: use a view for the subset of data that you need.

No I really must insert

Then your best bet is to use a trigger.

CREATE TRIGGER make_dups
    AFTER INSERT ON students
     FOR EACH ROW EXECUTE PROCEDURE trigger_function();

Where your trigger_function is a store function that creates the required row in the other table.

Now what about updates? Yes, you need a trigger for that too. What about deletes? Yes the same

Efficient method to Copy Data from one table to the other table in the same database

1 Answers1

Why Insert?

No I really must insert