1

I have been asked for my current project to convert our existing Oracle database into a Microsoft SQL database.

I have no problem for most of the data but I am having problems with a specific column.

I have a table with a CLOB column and with around 10,000,000 entries.

I tried to generate scripts with a update queries:

UPDATE DATABASE.TABLE SET CLOBCOLUMN = (CLOBCOLUMN + 'First part');
UPDATE DATABASE.TABLE SET CLOBCOLUMN = (CLOBCOLUMN + 'Added part');

At the end, I generated around 100GB of scripts to execute.

I tried to execute these scripts using sqlcmd:

echo off
set ScriptDir=<Directory with all scripts>
set MSQLAccount=<Connexion account>

FOR /L %%G IN (1,1,<last script number>) DO (
   sqlcmd -S %MSQLAccount% -i %ScriptDir%\script_%%G.sql 
)

pause

This solution is working but it is taking an infinite time: Only 14 entries are updated in 5 minutes ==> it will take around 8 years to finish.

Is there another solution that will help me to add these CLOB values from Oracle into the Microsoft SQL varchar(max) column?

Thank you in advance.

Triple
  • 11
  • 2
  • SSIS package? `insert into ... select ...` via linked server? Export the table into a flat file from Oracle and import it into MS SQL? There are plenty of options available. – Roger Wolf Sep 12 '18 at 12:30

0 Answers0