CODEWITHSUNDEEP
pythonsqloracle-database
EvanL
EvanL

Reputation: 73

Create copy of Oracle database table to another Oracle database using SQL

I am wondering if there is a simple way to copy either an entire Oracle database table or data rows into another Oracle database table (different databases). Is there some type of way to do this using SQL language? My initial thought is to use a SQL query like this:

INSERT INTO outputTable
SELECT * FROM username/[email protected]

If not with SQL, is there another way to do this? I am trying to automate this task so performing a copy using SQL Developer or something along those lines will not suffice unfortunately. I am trying to do this using Python.

Upvotes: 2

Views: 1892

Answers (3)

Aleksej
Aleksej

Reputation: 22969

You can create a DBLink to connect the source and the target DB, then simply use SQL to save your data in the linked DB.

Something like:

create database link "TARGET"
connect to YOUR_SCHEMA
identified by YOUR_PASSWORD
using YOUR_CONNECTION_STRING;

Once you have a DBLink, you can use plain SQL, no matter the involved tables are on different DB:

INSERT INTO YOUR_TABLE@TARGET SELECT * FROM YOUR_TABLE

Upvotes: 3

kayla210
kayla210

Reputation: 106

Try creating a database link: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm http://psoug.org/definition/create_database_link.htm

You'll only need to create the database link once, unless the credentials you need to connect with change at any point.

CREATE DATABASE LINK [Database Link Name] 
CONNECT TO [Username] IDENTIFIED BY [Password]
USING '[Service Name]';

Once the link is created, you can do something like this to retrieve data from a separate database and do whatever you want with it:

SELECT * FROM inputTable@[Database Link Name Here]

In this case:

SELECT * INTO outputTable
FROM inputTable@databaseLink

Upvotes: 1

chungtinhlakho
chungtinhlakho

Reputation: 930

have try openQuery? in sql server, you can link to an oracle database, after that you can just you it when Openquery

example

Select * into T_NewTable from openquery(connectionanme, 'select * from your source table')

Upvotes: 0

Related Questions