• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Data transfer between tables

 
shahabas shabeer
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi friends,
I have two similar tables say abc and abc_history.
I am inserting a lot of data to table abc.
At one point of time , I want to transfer a large volume of data from abc to abc_history.
Right now I am doing this in two steps as explained below
1. insert the data to abc_history from abc using an insert command.
2. delete the transfered data from abc.

But, unfortunately this process takes long time..
I would like to optimize this process.

Is there a way to do this in a single step..?
Is there a way to move data from one table to another..?

I am using postgres DB.

Thanks
Shahabas E Shabeer
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shahabas,
There is no single SQL command to insert and delete. A stored proc would do it in one "step" (from java's point of view), but there are still two commands in the stored proc. As the network is unlikely to be slowing things down, I would look at optimizing the queries. Also, you can disable indexes, run the commands and then enable the indexes.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How about trigger on delete. And yes disabling indices would surely give you a dramatic improvment.

Just delete the rows of the original table and trigger will insert all the deleted rows in history table.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Adeel Ansari:
How about trigger on delete.

Good point! I completely forgot about that.
 
shahabas shabeer
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks alot friends..
Let me try those tips.
 
shahabas shabeer
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just delete the rows of the original table and trigger will insert all the deleted rows in history table.

Hi Mr Ansaari,
Can you please post a simple example here ..
I searched the whole web for an example, but couldn't get any.
Thank you very much for your help.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is a way we make triggers in Oracle. Dont know how to in Postgres



May be you will find some by searching on Google with a string like "Trigger on delete postgres".
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic