Import Data From Caché To SQL Server 2012

Question

Import Data From Caché To SQL Server 2012

Lijun Zhang

Old Hand

Points: 337
More actions
December 20, 2018 at 8:45 am

#376666

Does anyone know what is the fastest method to import data from intersystems Caché to SQL Server? I am testing using linked server that is created using "Microsoft OLE DB Provider for ODBC Drivers" to import data. I need to import about 100 GB of data over a weekend. Assume my query uses the right indexes, is there any faster method than INSERT/SELECT statement like following?
Thanks.
INSERT
INTO TargetTable
SELECT *
FROM OPENQUERY (LinkedServer, 'SELECT * FROM SourceTable WHERE ...')

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 1

I would suggest exporting from Cache to text and then importing with bcp/BULK INSERT. Those items would probably work better for you.

Lijun Zhang Old Hand Points: 337 More actions · Answer 2

Steve Jones - SSC Editor - Monday, December 24, 2018 10:07 AM
I would suggest exporting from Cache to text and then importing with bcp/BULK INSERT. Those items would probably work better for you.

Thanks Steve. Do you know what is the fastest method to export data from Cache to text files?

Lijun

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 3

I don't. I've never used that system, but most technologies have quick text exports.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

from the old RTFM https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=ITECHREF_exporting

reading the manuals may identify other ways of doing it. and their forums probably have other info.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

I utilize the Managed .NET Provider for Intersystems Cache in my SSIS projects that extract from that platform. It works quite well and allows for managing the OLEDB Destination batch/commit sizes so I don't bloat the transaction log.

To enable the provider - you may have to manually add the provider to the machine.config file(s). The installation seems to add it to the x86 .NET 2 version - but misses the x64 and .NET 4...

machine.config files are located in:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG
C:\Windows\Microsoft.NET\Framework\v4.0.30319\CONFIG
C:\Windows\Microsoft.NET\Framework64\v2.0.50727\CONFIG
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\CONFIG

The provider needs to be added to the <DbProviderFactories> section in the <system.data> section and will be something like this:

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs