Export From Cache to Microsoft SQL Server

Question

Question

Roderick Julius · Mar 16, 2022

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

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

1 0

score 1 · Answer 1 · 2022-03-16T14:36:45-04:00

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.