CODEWITHSUNDEEP
sqlpostgresql
user7609786
user7609786

Reputation: 39

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

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

Upvotes: 1

Views: 153

Answers (1)

e4c5
e4c5

Reputation: 53774

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

Upvotes: 1

Related Questions