| copying data to a table (merged 6) [message #408322] |
Mon, 15 June 2009 14:30  |
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 #408387 is a reply to message #408322] |
Tue, 16 June 2009 01:36  |
Frank
Messages: 7901
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
