Transfering data from one database table to another database table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Transfering data from one database table to another database table

  1. #1
    Join Date
    Dec 2002
    Location
    Chennai, India
    Posts
    104

    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 ?

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Yes create a datbase link..and then search for sqlplus copy command here on this forum..it is quite easy..

    regards
    Hrishy

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  4. #4
    Join Date
    Dec 2002
    Location
    Chennai, India
    Posts
    104
    Hi,
    But then how can i transfer this temp table data to another db table? again I have to use db_link?

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Dec 2002
    Location
    Chennai, India
    Posts
    104
    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

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Feb 2001
    Posts
    203
    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

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

  11. Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  



    Click Here to Expand Forum to Full Width