|
-
Transfering data from one database table to another database table
Hi Guys,
I have to two databases and have to transfer around 6 Lacs of record from one of the database tables to another database table.(schema for the two tables are same).Right now my ODBC application is doing this using cursors,but some memory usage problem is there(CPU usage goes to 100%).
So I would like to know if any other ways by which i can execute a query to transfer this bulk record.
Can I use 'select * from X to Y where condn=xx' with the help of database link(Y)? If so is any size limitation for this select transfer ?
-
Hi
Yes create a datbase link..and then search for sqlplus copy command here on this forum..it is quite easy..
regards
Hrishy
-
Its not a better practice ( i think i can say this ) to have DB link and transfer data by comparing data on remote DB..This will only slow up process.
Rather use COPY cmd to load into Temp_Table & now u can play with this data.
BTW, Hrishy Long time since you posted?
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi,
But then how can i transfer this temp table data to another db table? again I have to use db_link?
-
No copy command doesnot need a database link.
And BTW Abhay on what basis have you said that using database link is not a good practise. What process gets slowed down? Either way you need to make a database connection. So whats wrong with database links.
Amar
"There is a difference between knowing the path and walking the path."
 
-
Hi
My requirement is to transfer data from one table to another table of different database. So I think sql copy command only for copying tables between databases and this won't help.
Can I use like 'Insert into T2 as select * from T1 where '
where T1 is a db_link name for the second database and T1 is a table in the first database
-
Read the SQL reference guide or just give a copy command on sql prompt.
Code:
sys@ACME.WORLD> copy
usage: COPY FROM TO { () } USING
: database string, e.g., scott/tiger@d:chicago-mktg
: ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
: name of the destination table
: a comma-separated list of destination column aliases
: any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.
sys@ACME.WORLD>
Im not able to show the exact syntax as the thread is chooping off few parts...
Last edited by adewri; 07-15-2003 at 12:55 PM.
Amar
"There is a difference between knowing the path and walking the path."
-
Hi sudheer_nm,
I am assuming You have tables like this.
db1---emp_table
db2---emp_table
If it's correct then by using sql copy command you can copy data
from db1 database(emp_table) to db2 database(emp_table).
This is one of the powerfull option to transfer data from one table to another table. Read about this command and let us know if you need help.
Sree.
sree
-
Originally posted by adewri
And BTW Abhay on what basis have you said that using database link is not a good practise. What process gets slowed down? Either way you need to make a database connection. So whats wrong with database links.
Frist let me list defects in using DB link.
* Password of Remote DB can be known to any user who has got sufficient privilages to see sys objects. Even when DB link is private to the user x, user y can know the login credentials of the remote DB.
* Causes memory/stack overflow at times & this may lead to abnormal shutdown of the DB.
* Many bugs reported while using DB Link.
Now how would DB Link slow up process
* NOT IN clauses wont just turn up over DBs if Tables in Q are big.
* Even EQUI joins will eat time, due many round trips via N/W.
Rather, you can do some think like this ( On Local Sever )
* Create Temp_Table.
* Load data from remote DB through COPY ( by setting arraysize conisderably high ).
* Pick the data from temp_table load it to main table using Insert..Select ( the desired rows ).
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
Frist let me list defects in using DB link.
* Password of Remote DB can be known to any user who has got sufficient privilages to see sys objects. Even when DB link is private to the user x, user y can know the login credentials of the remote DB.
Any way you need the password for connecting to remote database.
Originally posted by abhaysk
* Causes memory/stack overflow at times & this may lead to abnormal shutdown of the DB.
When ??? never faced such problem. Have been replicating databases , moving data to and fro via PL/SQL for last three years over db links, never faced such problem.
Originally posted by abhaysk
* Many bugs reported while using DB Link.
Name a few. Database link is nothing but simply a connect username/password@remotedb.
Originally posted by abhaysk
Now how would DB Link slow up process
* NOT IN clauses wont just turn up over DBs if Tables in Q are big.
* Even EQUI joins will eat time, due many round trips via N/W.
Rather, you can do some think like this ( On Local Sever )
* Create Temp_Table.
* Load data from remote DB through COPY ( by setting arraysize conisderably high ).
* Pick the data from temp_table load it to main table using Insert..Select ( the desired rows ).
Abhay.
What ever you said will be true for a normal connection as well.
Amar
"There is a difference between knowing the path and walking the path."
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Click Here to Expand Forum to Full Width
|
|
Reply With Quote
