Solved

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

Posted on 2014-11-10
6
318 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:Praveen Kumar Chandrashekatr
Praveen Kumar Chandrashekatr 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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:Praveen Kumar Chandrashekatr
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

688 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