Home » SQL & PL/SQL » SQL & PL/SQL » copy or move table (9i)
copy or move table [message #332310] Tue, 08 July 2008 03:08 Go to next message
alijeyan
Messages: 180
Registered: January 2007
Location: IRAN
Senior Member
Hi All

I have 2 DataBase : One DataBase in SERVER and i connect from client and One DataBase in My Local System .
How I can Move or Copy a Table from one database to another database with SQL Command ?
I use this command in SQL navigator:

create table DB1.User1.Table1 as
( select * from DB2.User2.Table2)

but this not work !

I can not use Import and Export in my network office !

Please Help Me.
ThanQ
Re: copy or move table [message #332312 is a reply to message #332310] Tue, 08 July 2008 03:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you mean when you say 'Does not work'
Do you get an error message?
If so, what it is?
Re: copy or move table [message #332318 is a reply to message #332312] Tue, 08 July 2008 03:37 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
How will you access remote table in your database??
You have to attach service name with table2@<service_name>

Other way is SQL*PLUS Copy Command

Regards,
Rajat
Re: copy or move table [message #332325 is a reply to message #332310] Tue, 08 July 2008 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
create table DB1.User1.Table1 as
( select * from DB2.User2.Table2)

This is not an Oracle syntax, are you using Oracle?

Regards
Michel
Re: copy or move table [message #332344 is a reply to message #332325] Tue, 08 July 2008 04:35 Go to previous messageGo to next message
alijeyan
Messages: 180
Registered: January 2007
Location: IRAN
Senior Member
Hi michel
Yes . I use Oracle . I Want copy a table with data from a DataBase(DB1) to another Database(DB2) .
I use SQL NaviGator . and i can connect to 2 Database in SQL navigator (DB1 & DB2) but i can not move or copy Table from DB1
to DB2 .
my question is : what is SQL command for copy a table from a
connection to another connection ?

Regard ALI
Re: copy or move table [message #332347 is a reply to message #332344] Tue, 08 July 2008 04:55 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Hints are already provided my freind.

First think about selecting data from remote database
table in your local database.

select * from remote_table@<service_name>

where <service_name> is the service to connect remote database.

Regards,
Rajat
Re: copy or move table [message #332348 is a reply to message #332347] Tue, 08 July 2008 05:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, to use the @ notation, you need to have created a Database Link

In this case the syntax you would used to create a link from DB1 to DB2 will look a lot like:
CREATE DATABASE LINK temp_link CONNECT TO user2 identified by <user 2 password> USING 'DB2'


You would execute this on DB1.
Then, while still connected as User1 on DB1 you can do
create table Table1 as
( select * from Table2@temp_link)
Re: copy or move table [message #332350 is a reply to message #332348] Tue, 08 July 2008 05:04 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Sorry i missed that Shocked

Regards,
Rajat

[Updated on: Tue, 08 July 2008 05:05]

Report message to a moderator

Previous Topic: DATE FORMAT
Next Topic: SQL QUERY for distinct items
Goto Forum:
  


Current Time: Wed Dec 07 12:45:37 CST 2016

Total time taken to generate the page: 0.12283 seconds