• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Data transfer between tables

 
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 42105
933
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 42105
933
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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".
 
Beauty is in the eye of the tiny ad.
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic