Solved

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

Posted on 2014-11-10
6
314 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

772 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