0
1002
Question Roderick Julius · Mar 16, 2022

Export From Cache to Microsoft SQL Server

#Caché #Data Import and Export

Hello,

Our team is looking for a way to export all of our Cache SQL tables into Microsoft SQL Server.  I have only found a method to export one table at a time into an ASCII file.  We have over 170 tables so this would be very tedious and time consuming.  Is there a way to directly export from Cache to SQL Server.  Alternatively is it possible to export the entire database in a single shot or even multiple tables to text files?

Thanks!

Product version: Caché 2018.1
$ZV: Cache for Windows (x86-64) 2018.1.4 (Build 505_1_20258U)
Discussion (2)0
Log in or sign up to continue

Comments

Luc Morningstar · Mar 16, 2022

in Caché  $system.SQL.Export()  writes all DDL to file  
adjustment for MS SQL might be required.

next you loop in a routine with SELECT * from ....<your tables>  and write it to external files
the appropriate methods are in %SQL.Statement. 

0
Vitaliy Serdtsev · Mar 17, 2022

You can use Caché SQL Gateway (Link Table via JDBC or ODBC)
This, your data migration query will be of the form:

insert into mssql.table(field1,..,fieldN) select field1,..,fieldN from cache.table

Or use third-party utilities to migrate data from/to any DBMS via JDBC/ODBC, for example SQL Data Lens (aka Caché Monitor): Local query cloud (there is a video)

Also there the Bulk Export

@Andreas Schneider - the author of this tools

0