Solved

Copy replace  data from one column from database A table A to another database B table B

Posted on 2014-11-10
6
316 Views
Last Modified: 2014-12-01
I am trying to copy and replace column C from Database A table A to another Database B table B ...

Please note column C exist in both databases we are trying to replace it.

Database A uses different credentials and database B uses different credentials.

Here is what we are trying to work with ... problem is how we handle connections within the database and how we can replace the column
0
Comment
Question by:CalmSoul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 40433686
create database link from A to B using whatever credentials needed for B.

then do something like this in A.

update tableB@dblink_to_B set C = (select C from tableA where tableA.id = tableB.id);

Change the where clause to whatever unique identifiers are appropriate to connect one row in A to one row in B.
This will update every row in tableB.  If the subselect on A can't find a value, then that row in B will be set to NULL.


If that's not what you want, then you'll probably want to add a where clause on B to only update the rows you're interested in.
0
 
LVL 5

Author Comment

by:CalmSoul
ID: 40433705
create database link from A to B using whatever credentials needed for B.

--How to create that link? please assist
0
 
LVL 12

Assisted Solution

by:praveencpk
praveencpk earned 250 total points
ID: 40433921
To create a DB Link you should have create database link privileges or system/sys user login credentials.

SQL>create database link <link_name> connect to scott identified by <pass> using 'TNSNAMES';

Here is the oracle doc on DB Link creation.
https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_admin002.htm#ADMIN12156
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:CalmSoul
ID: 40433970
What is Scott in your example? Database name or schema name?
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 40433982
Scott is an example for userid.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40434912
sorry didn't see your reply until today.  Yes, you need to create the link and fill in your db name, username and password
Note, the db name (tns alias) is in quotes, the linkname, username and password are not

create database link DBLINK_TO_B
connect to YOUR_B_USER
identifed by YOUR_B_PASSWORD
using 'YOUR_B_DATABASE';
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Distinct values from all columns in a table?? PL SQL 4 58
run sql script from putty 4 175
best datatype for oracle table email creation 8 75
SQL query for highest sequence 4 61
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question