0

Question about the import / export process.

I know is SQL Developer I can export all the columns from a table and import into the same table on another server.

My question is - what happens with the duplicate inserts? Will it just skip or what will happen?

I have these tables I need the data to be identical - one of the tables has data the other doesn't and I need to make them match without it messing up what is already in there.

My theory is that once I export all of the rows in the form of inserts and run it on the other DB-Table it will insert all the ones it's missing and error and skip the inserts where there already exsist the row, but want to make sure this is correct.

Thanks in advance!

Improve this question
5
  • Have you looked at Oracle's MERGE statement?
    – Vérace
    Commented Aug 13, 2015 at 16:19
  • From my understanding it would not work because even though the table names and structure are identical, the two tables are on different databases and different servers. Is that correct?
    – seerick
    Commented Aug 13, 2015 at 16:22
  • Not as I understand it. Again, as I understand it, yours is the very scenario for which MERGE is suited. Why would you have two tables with identical structures on a single server? That's redundancy, a mortal sin for database professsionals! Its very raison d'être is for transferring data between servers.
    – Vérace
    Commented Aug 13, 2015 at 16:41
  • Ok, I will dig into this option further - I found that I may have to create some sort of link between the two DB's but not sure at this point. Thank you for the feedback
    – seerick
    Commented Aug 13, 2015 at 16:49
  • The link can be through SQL Developer - MERGE stuff FROM db1.table1 INTO db2.table2 (or whatever the syntax is). Reading your comment to the answer - it seems to me that MERGE is the perfect solution.
    – Vérace
    Commented Aug 13, 2015 at 17:00

1 Answer 1

Reset to default
0

If you have duplicate inserts, the first question I would have would be why would you have two rows in the same table with the exact same information?

That being said, if there are no unique keys or other such constraints on the table, the new DB server should dutifully import the duplicated data.

I would heartily recommend testing it in a non production environment first, though.

Improve this answer
3
  • Yes, it's our DEV that is out of sync with TEST Dev has the info we need so first would be testing in Test. I think I led you wrong. I don't have duplicate info in same Table. Dev has rows X Y Z while TEST only has X Z So, I need to get Row's Y over to TEST. I exported all of the rows from the tables in question from DEV and equals about a 1GB of inserts. Some of which I need while other I dont because they already exist - X and Z I just wanted to make sure if I ran the script in TEST to insert everything from DEV - The end result would be that they now match. Does that make sense?
    – seerick
    Commented Aug 13, 2015 at 16:10
  • Yes. That makes perfect sense. I would definitely back up the data first, but as long as there is no truncate statement or some such, and the import is set to ignore duplicate inserts rather than throwing a constraint error, they should both match at the end of the process. This is, in essence, what the MERGE command does.
    – stubsthewizard
    Commented Aug 13, 2015 at 17:14
  • Thanks a ton, I'm going to get some more folks in the team a little more knowledgeable of the data/system and present both this and the MERGE statement option and get started on Testing. Thanks again!
    – seerick
    Commented Aug 13, 2015 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.