Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2011
    Posts
    27

    Question Unanswered: Select and Insert

    I wish to SELECT from DB1 and INSERT to DB2 respectively in single SQL Query.

    For example :
    DB1
    Select username,account_status from dba_users;

    DB2
    Insert the above values in a table "ID Details"

    Database link can't be used due to security and company policy

    thanks
    Gopalan v

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I wish to SELECT from DB1 and INSERT to DB2 respectively in single SQL Query.
    >Database link can't be used due to security and company policy

    mutually exclusive requirements.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    27
    Quote Originally Posted by anacedent View Post
    >I wish to SELECT from DB1 and INSERT to DB2 respectively in single SQL Query.
    >Database link can't be used due to security and company policy

    mutually exclusive requirements.
    I know, but thats the fact...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I know, but thats the fact...
    let us know which one gets changed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2011
    Posts
    27
    But are you sure that it can't be done without DB links ? I can go back and fight to my lead.

    Thanks
    gopi

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But are you sure that it can't be done without DB links ?
    Basic LOGIC 101 - you can NOT prove a negative.
    so "NO" I can not prove that it can NOT be done.
    DB LINK is only way I know to move data from 1 DB to another in a single transaction.
    You can export , sneaker net file, & then import as an alternative.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Oct 2011
    Posts
    27
    Thank you...

    Kindly help me with your SQL query...

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    INSERT INTO TABLEA AS SELECT * FROM TABLEA@REMOTE

    above pulls data from remote DB & below pushes data into remote DB

    INSERT INTO TABLEA@REMOTE AS SELECT FROM TABLEA
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    One other way, Desepported but still in the database. The COPY command. See

    SQL*Plus COPY Command
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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