Re: ADO&Oracle bulk inserts ( what else! )

I'm assuming the source table is not within Oracle ?

I've had success with using SQL*Loader from .DAT files. I had to export tables from SQLServer2000 using BCP. As far as I know, it is the most efficient way to import data from outside Oracle.

Some shortcomings of SQL*Loader show up when you have CLOB columns with carriage returns/line feeds. I ended up replacing those non-printable characters with a temporary character string when creating the .DAT file and then replacing in the target table after the load completed. I suppose there are better ways to do this and I'd be glad to hear any other thoughts on this topic.

Kurt

-- 


----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

"JohnP" <johnp_at_optonline.net> wrote in message
news:BXMcd.3646$YM4.761411_at_news4.srv.hcvlny.cv.net...


> I need to write an ado app that reads a 27million record table, process it

> and insert it into a new table.

> Unfortunately I have no choice on the use of ADO.

>

> My question is, on the inserts am I better off using a Command object and

> specify not to return any record sets. Or should I use the Recordset

object


> and specify lockbatchoptimistic?

>

> I know I should check to see if the 0040 driver/interface is being used.

>

> Also, for the reads and writes should I use client side or server side

> cursors. I really do not need any transaction processing. The table will

not


> be accessed during the process.

>

> So far it seems that writing the data to a text file and then using the

bulk


> insert utility( or whatever it is called) is the fastest but again that is

> not desired by others.

>

>

> So any feed back is greatly appreciated

>

>

> johnp

>

>