3

I am currently assisting in a migration effort of this application from Oracle 12c to SQL Server 2017. Initially I just performed table inserts using openquery to Oracle. I discovered that the tables which contained CLOB data could not be migrated using a single table to table insert query otherwise we would end up with dirty data. I am able to do the inserts by dynamically generating a single insert statement per row and running thousands of lines of insert statements. The problem is I've now come across a table which contains over 300k records with CLOB data in them. The one record at a time insert is taking an extremely long time and may at this rate run over 24hrs which is unacceptable.

What is my best option for migrating such a large amount of CLOB data from Oracle? Should I use the bulk tools like BCP, BULK INSERT or OPENROWSET?

Edit/Update: I have since learned that my troubles are most likely due to the UTF-8 encoding at the source Oracle system. Both methods I've used for migrating both CLOB and BLOB data have resulted in mismatched rows or missing rows.

UTF-8 has some known issues with SQL Server. The ones in particular I'm dealing with, Linked Server OPENQUERY and SSIS Project deployments, are both fixed in SQL Server 2019.

9
  • 2
    Any reason you're not using SSIS? – mustaccio May 1 '19 at 17:16
  • It's not a feature we have used and I'm still fairly new to it. Is it worth learning it to be able to accomplish this? – Geoff Dawdy May 1 '19 at 17:27
  • Yes I think it's worth consideration. – mustaccio May 1 '19 at 18:24
  • Can you please explain this statement: "_ tables which contained CLOB data could not be migrated using a single table to table insert query otherwise we would end up with dirty data._" Is this an encoding issue? How are you bringing this data over? Linked Server? – Solomon Rutzky May 1 '19 at 19:47
  • 1
    hi @SolomonRutzky. When I performed an INSERT INTO SELECT * from openquery to the oracle database, it appeared that the rows were coming over but the CLOB column had missing data. I didn't have enough time to sit down and figure out why it was doing it so I just moved on. – Geoff Dawdy May 1 '19 at 20:09
0

You could check out - https://www.attunity.com/products/replicate/attunity-replicate-for-microsoft-migration/

"Attunity Replicate for Microsoft Migrations is a special, no cost offering for Microsoft customers to help migrate data from popular databases to the Microsoft Data Platform."

2
  • That's worth a shot but at this time I only have a .edu email and my personal email. The download page only accepts business emails :( – Geoff Dawdy May 2 '19 at 13:49
  • I have started to look into SSIS and Attunity, any help here would be appreciated. – Geoff Dawdy May 6 '19 at 23:33

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