4

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.

Improve this question
9
  • 2
    Any reason you're not using SSIS?
    – mustaccio
    May 1, 2019 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, 2019 at 17:27
  • Yes I think it's worth consideration.
    – mustaccio
    May 1, 2019 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, 2019 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, 2019 at 20:09

1 Answer 1

Reset to default
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."

Improve this answer
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, 2019 at 13:49
  • I have started to look into SSIS and Attunity, any help here would be appreciated.
    – Geoff Dawdy
    May 6, 2019 at 23:33

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