Export From Cache to Microsoft SQL Server
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!
Comments
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.
You can use Caché SQL Gateway (Link Table via JDBC or ODBC) This, your data migration query will be of the form:
<FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">mssql</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">table</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">field1</FONT><FONT COLOR="#000000">,..,</FONT><FONT COLOR="#008000">fieldN</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">field1</FONT><FONT COLOR="#000000">,..,</FONT><FONT COLOR="#008000">fieldN </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">cache</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">table</FONT>
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