Solved

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

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

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

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ā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

828 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