CODEWITHSUNDEEP
sql-serveroracle-databaseexportclobvarcharmax
Triple
Triple

Reputation: 11

Convert Oracle CLOB column into Microsoft SQL varchar max

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.

Upvotes: 1

Views: 1478

Answers (0)

Related Questions