-1

I’m in the middle of trying to migrate a large amount of data into a oracle database from existing excel-files. Due to the large amount of rows loaded (10 000 and more) every time, it is not possible to use SQL Developer for this tasks. In every work-sheet there’s data that need to go into different tables, but at the same time keep the relations and not dropping any data.

As for now, I use one .CSV file for each table and mapping them together afterwards. This is thou combined with a great risk of adding the wrong FK and with that screw up the hole shit. And I don’t have the time, energy or will for clean ups even if it is my own mess…

My initial thought was if I could bulk transfer with sql loader using some kind of plsql-script in maybe an ctl-file (the used for mapping the properties) but it seems like I.m quite out in the bush with that one… (or am I…? ) The other thought was to create a simple program In c# and use fastMember and load the database that way. (But that means that I need to take the time to actually make the program, however small it is).

I can’t possible be the only one that have had this issue, but trying to us my notToElevatedNinjaGoogling-skills ends up with either using sql developer (witch is not an alternative) or the bulk copy thing from sql load (and where I need to map it all together afterwards).

Is there any alternative solutions for my problem or is the above solutions the one that I need to cope with?

anbj
  • 1
  • 1
  • 3
    What does this mean? "Due to the large amount of rows loaded (10 000 and more) every time, it is not possible to use SQL Developer for this tasks." 10k is a SMALL amount, and presents no problems in SQL Developer. – thatjeffsmith Apr 29 '19 at 18:47
  • load all of the data to your tables, THEN add your foreign keys...if there are orphaned rows that prevent the FK constraints from being created, use SQL to ID them and add the missing required lookup entries – thatjeffsmith Apr 29 '19 at 18:48
  • I was not able to make it work, even if it considered a SMALL amount. And yes, loading the tables and then adding FK is a good thing to do. But what worries me is the risk of adding the wrong FK, ergo giving wrong references. – anbj Apr 29 '19 at 20:08
  • 1
    what happened when you tried, what does 'make it work' mean? you got an error? it took too long, what? – thatjeffsmith Apr 29 '19 at 21:04
  • if the fk is bad, your data is bad. if your data is bad, then the fk's will help protect your good data...so is your data good or bad? – thatjeffsmith Apr 29 '19 at 21:04
  • I could load about 500 rows without problem. The only error I got was that something went wrong and the action were rolled back. But It took about 10-15 min for that error to occur with several "not responding" events. I'm not sure what you mean with good or bad data. Could you explain? And, for the record, I'm truly sorry for my lack of details. My only defense is that I'm still learning, so thanks for your patient! – anbj Apr 30 '19 at 08:24
  • so when that happens, set the load mode to 'Insert Script', then we'll run through your data and generate INSERT statements instead. Then you can run that in a sql worksheet and see where the bad data is. – thatjeffsmith Apr 30 '19 at 13:04
  • 1
    but holy mother of ... God... I've totally missed the 'Insert Script' function... I will now go and sit in the corner of shame... Thanks for the help and for your patient! – anbj May 01 '19 at 18:31
  • no worries, we all have those moments, even 20 years into our careers! – thatjeffsmith May 02 '19 at 00:37

1 Answers1

0

Did you consider using CSV files as external tables? As they act as if they were ordinary Oracle tables, you can write (PL/)SQL against them, inserting data into different tables in the target schema. That might give you some more freedom & control over what you are doing.

Behind the scene, it is still SQL*Loader.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 1
    and the GUI will help you do it too https://www.thatjeffsmith.com/archive/2015/08/loading-data-via-external-tables-fast/ - you'll just need access to the server. if you don't, then we can help you with sqlldr too https://www.thatjeffsmith.com/archive/2018/10/yes-sqlloader-is-faster-at-loading-records/ – thatjeffsmith Apr 29 '19 at 18:54