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.

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.

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... 07-15-2003, 12:58 PM sree_sri 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. 07-16-2003, 03:29 AM abhaysk Quote: 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. 07-16-2003, 04:35 AM adewri Quote: 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. Quote: 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. Quote: 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. Quote: 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. Show 40 post(s) from this thread on one page Page 1 of 3 123 Last Jump to page: All times are GMT -4. The time now is 04:39 AM.