Home » SQL & PL/SQL » SQL & PL/SQL » copying data to a table (merged 6)
copying data to a table (merged 6) [message #408322] Mon, 15 June 2009 14:30 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
Its a little tricky to explain the situation but I would try to do the best.

I have a table systems in schema ericd and another table systems in a different schema ericw. Both these tables have the same structure but completely different data. However it was decided lately that both of these tables need to be consolidated, in such a way that data from ericd would be moved to systems table in ericw.

1) Find the max id in table systems in ericw.
2) store it in a variable.
3) copy data from ericd.systems to ericw.systems in a way that the additional fields start with an increment to the variable stored in step 2 above.


Could you advise how would I do this? My guess is I would store the variable as

declare @count int;
SET @count = (select max(system_id) from erivw.systems);
show @count;

and then use @count in the query to move data, but I am not sure how.

help appreciated.
Re: copying data to a table (merged 6) [message #408330 is a reply to message #408322] Mon, 15 June 2009 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Spamming the forum with too pmuch same question is the good way to get an asnwer.

Read OraFAQ Forum Guide and post accordingly.

Regards
Michel
Re: copying data to a table (merged 6) [message #408332 is a reply to message #408330] Mon, 15 June 2009 14:47 Go to previous messageGo to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
sorry about that, it was not intentional my browser apparently got stuck.

Thanks
Re: copying data to a table (merged 6) [message #408333 is a reply to message #408322] Mon, 15 June 2009 14:51 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>my browser apparently got stuck.
browser is incapable of CLICKING Submit!
PEBKAC

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: copying data to a table (merged 6) [message #408387 is a reply to message #408322] Tue, 16 June 2009 01:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
kilyas wrote on Mon, 15 June 2009 21:30

declare @count int;
SET @count = (select max(system_id) from erivw.systems);
show @count;

and then use @count in the query to move data, but I am not sure how.

help appreciated.

You do realize that this is an Oracle specific forum, don't you?
Re: copying data to a table (merged 6) [message #408456 is a reply to message #408387] Tue, 16 June 2009 05:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that you want an Oracle solution, and not a SQL Server one, I'd do it like this.

1) find the maximum id
2) Create a sequence that starts with the max value + 1
3) create a Row level on insert trigger on Ewricw.Systems that replaces the id value with the next value off the sequence created in 2)
4) INSERT INTO ericw.systems SELECT * FROM ericd.systems.
Previous Topic: Add a new hash partition to existing table
Next Topic: how to migrate the encrypted data from one data base to another data base
Goto Forum:
  


Current Time: Sat Dec 03 01:22:53 CST 2016

Total time taken to generate the page: 0.13141 seconds