Is it OK to use this partially rebuilt index if I don't care about data that isn't indexed? Did you do this before?

I didn't build indexes manually, except for tests. If the index is not built for all data, then the query will see only the data for which the index exists.

 
Try simple example:

If we open the source code of the class for the Status field, we will see the following:

Class Ens.DataType.MessageStatus Extends %Integer
{

Parameter DISPLAYLIST = ",Created,Queued,Delivered,Discarded,Suspended,Deferred,Aborted,Error,Completed";

Parameter VALUELIST = ",1,2,3,4,5,6,7,8,9";

}

Therefore , the following conclusions can be drawn:

  1. For Embedded SQL the RuntimeMode is Logical
  2. For SQL Explorer (Portal) the RuntimeMode is Display

Hence the leapfrog in the results.

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,..,fieldNselect 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

See

$$TableName^%occLGUtil(pClass) (taken from sources %DeepSee.Report.Model.Report)
##class(%DeepSee.Utils).%GetSQLTableName(pClass)
##class(%ZEN.DataModelUtils).GetSQLTableName(pClass)
##class(%DeepSee.Generator).%GetSQLTableName(pClass) (my choice)

It is worth remembering that the table name can be a reserved word, so you need to put it in quotation marks.

For example, the method %DeepSee.Generator:%GetSQLTableName does not always work correctly for Caché:

Class count.sum.select Extends %Persistent SqlTableName "current_date.max" ]
{

ClassMethod TableName(which 0) [ CodeMode = objectgenerator ]
{
  set sch=%compiledclass.SqlSchemaNametab=%compiledclass.SqlTableName
  do %code.WriteLine($c(9)_"quit $p("""_sch_"."_tab_","_sch_","_tab_""","","",which+1)")
  quit $$$OK
}

/// d ##class(count.sum.select).Test()
ClassMethod Test()
{
  i=0:1:2 ..TableName(i),!

  pClass=$classname()
  !,##class(%DeepSee.Generator).%GetSQLTableName(pClass),!

  w $$TableName^%occLGUtil(pClass),!
  ##class(%DeepSee.Utils).%GetSQLTableName(pClass),!
  ##class(%ZEN.DataModelUtils).GetSQLTableName(pClass),!
}

}

USER>##class(count.sum.select).Test()
count_sum.current_date.max
count_sum
current_date.max
 
count_sum."current_date" ERROR!
count_sum.current_date.max
count_sum.current_date.max
count_sum.current_date.max

In IRIS 2021.2, an error occurs yet at the compilation stage of the class.

Try the following:

  1. enable "Retain cached query source" as described above
  2. compile your class/project with addition flags "k /compileembedded=1" (see Validating Embedded SQL Code)
  3. open the code of the generated program from the Studio or System Portal like %sqlcq.[YOURNAMESPACE].xETs3uMe2Ehn2LY5inuZiK4xgwnS.1.int
  4. Note: at the same time, do not forget to specify checkbox for system items

  5. Profit!