Home » RDBMS Server » Server Utilities » Import without dropping tables (Oracle xe,xp)
Import without dropping tables [message #309015] Wed, 26 March 2008 04:04 Go to next message
andi_raj
Messages: 55
Registered: February 2008
Member
I want to import/export tables between two PCs with same user name. while doing import i have to drop all the tables then import because new datas are not getting appended. each and every time i have drop all the tables. is there any way? kindly help. thanx in advance.raj
Re: Import without dropping tables [message #309018 is a reply to message #309015] Wed, 26 March 2008 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to do it by yourself, there is no option "REPLACE" on import.

Regards
Michel
Re: Import without dropping tables [message #309053 is a reply to message #309015] Wed, 26 March 2008 05:30 Go to previous messageGo to next message
andi_raj
Messages: 55
Registered: February 2008
Member
what should i do myself? okay let me put it in this way. PC 1 - one table 10 rows. i add two rows. PC-2 same user, same table 10 rows. If import the table from PC 1 into PC2 with out dropping only 10 rows are there. The newly added two rows are not getting loaded. what should i do? thanks in advance. raj
Re: Import without dropping tables [message #309057 is a reply to message #309053] Wed, 26 March 2008 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Truncating the table before importing.
Doesn't this you want?

Regards
Michel
Re: Import without dropping tables [message #309074 is a reply to message #309053] Wed, 26 March 2008 06:36 Go to previous messageGo to next message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
andi_raj
The newly added two rows are not getting loaded
Yes, they are. Eventually.

Here's an example: I have precreated both tables (one in Scott's, and the other one in Mike's schema) and inserted several records (note that there are NO unique constraints there!):
SQL> connect scott/tiger@ora10
Connected.
SQL> select * From test;

        ID
----------
         1
         2

SQL> connect mike/lion@ora10
Connected.
SQL> select * From test;

        ID
----------
         0
         1

SQL>

Now, exporting Scott's table (excerpt):
M:\>exp scott/tiger@ora10 tables=test file=test.dmp

. . exporting table                           TEST          2 rows exported

Let's import it into Mike's schema (excerpt):
M:\>imp mike/lion@ora10 file=test.dmp ignore=y full=y

. importing SCOTT's objects into MIKE
. importing SCOTT's objects into MIKE
. . importing table                         "TEST"          2 rows imported

Now let's check how it looks like:
M:\>sqlplus mike/lion@ora10

SQL> select * from test;

        ID
----------
         0
         1
         1
         2

SQL>

If I'm not wrong, Scott's records ARE appended to Mike's ones. There would be unique key constraint violation if it existed (so record with ID = 1 would fail to be imported).

If this example doesn't help, could you post YOUR OWN example so that we could see what you are doing (and, possibly, what you'd like to achieve).

[Updated on: Wed, 26 March 2008 06:37]

Report message to a moderator

Re: Import without dropping tables [message #309383 is a reply to message #309015] Thu, 27 March 2008 04:58 Go to previous messageGo to next message
andi_raj
Messages: 55
Registered: February 2008
Member
hi thanks a lot for explaining with an example. here's the scenario.
pc : 1
user: raj
table:t1
id
--
1
2
3
4

pc : 2
user:raj
table:t1
id
--
1
2

when i export from pc1 and import into pc2 the result should be as follows:
pc :2
user:raj
table:t1
id
--
1
2
3
4
is it possible? thanx. raj
Re: Import without dropping tables [message #309393 is a reply to message #309383] Thu, 27 March 2008 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a unique key on your column.

Don't you see the differences between your post and the other ones. Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Import without dropping tables [message #309428 is a reply to message #309383] Thu, 27 March 2008 07:07 Go to previous message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why don't you read the whole message?
LF
There would be unique key constraint violation if it existed (so record with ID = 1 would fail to be imported).
Previous Topic: IMPORT and EXPORT
Next Topic: 9i data import to 10g
Goto Forum:
  


Current Time: Mon Dec 05 02:40:53 CST 2016

Total time taken to generate the page: 0.07064 seconds