Solved

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

Posted on 2014-11-10
6
307 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.

Join & Write a Comment

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 …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 how to recover a database from a user managed backup

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now