When importing a table-view from MSSQL with more than 1000 rows I get an error:

The full error: select * from [CAMPGIFTS] offset @p0 rows fetch next @p1 rows only - Invalid usage of the option next in the FETCH statement.

I notice there isn't an "order by" in the select statement but I believe it's required. I don't know where to fix this or how but hopefully some there can help me get this solved.

So I figured out that I could get at least the tables with a primary key and set the sort order to the constraint definition. Seems like there should be a way to combine these into one script or integrate it into the import routine.

SQL

use sqlDbName
select 'importTable({sourceTable: "'+OBJECT_NAME(parent_object_id)+'",destCollection: "'+OBJECT_NAME(parent_object_id)+'",select: "*",sortField:"'+pk.[column]+'",sortOrder:"'+CASE WHEN pk.is_desc = 0 THEN 'ASC' ELSE 'DESC' END+'",fieldMap: {}});' knexHelper
FROM sys.objects o
LEFT JOIN ( SELECT
cstr.[object_id],
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
cstr.name AS [ForeignKey_Name],
fk.constraint_column_id AS [ID],
cfk.name AS [Name],
crk.name AS [ReferencedColumn],
OBJECT_NAME(fk.referenced_object_id) [referenced_table]
FROM sys.tables AS tbl
INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.[object_id]
INNER JOIN sys.foreign_key_columns AS fk ON fk.constraint_object_id=cstr.[object_id]
INNER JOIN sys.columns AS cfk ON fk.parent_column_id = cfk.column_id and fk.parent_object_id = cfk.[object_id]
INNER JOIN sys.columns AS crk ON fk.referenced_column_id = crk.column_id and fk.referenced_object_id = crk.[object_id]) fk ON fk.[object_id] = o.[object_id]
LEFT JOIN ( SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
OBJECT_ID(tbl.[name]) [table_id],
i.name AS [Name],
CASE WHEN 'FG'=dsi.type THEN dsi.name ELSE '' END AS [FileGroup],
i.ignore_dup_key AS [IgnoreDuplicateKeys],
i.fill_factor AS [FillFactor],
CAST(INDEXPROPERTY(i.[object_id], i.name, N'IsPadIndex') AS bit) AS [PadIndex],
~i.allow_row_locks AS [DisallowRowLocks],
~i.allow_page_locks AS [DisallowPageLocks],
s.no_recompute AS [NoAutomaticRecomputation],
c.[column],
c.[is_desc]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.[object_id]=tbl.[object_id])
INNER JOIN (
SELECT ic.[object_id], c.[name] [column], ic.is_descending_key [is_desc]
FROM sys.index_columns ic
INNER JOIN sys.indexes i ON i.[object_id] = ic.[object_id] AND i.index_id = 1 AND ic.index_id = 1
INNER JOIN sys.tables t ON t.[object_id] = ic.[object_id]
INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.column_id = ic.column_id
) AS c ON c.[object_id] = i.[object_id]
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.[object_id] AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.[object_id] = i.[object_id] AND xi.index_id = i.index_id
LEFT OUTER JOIN sys.stats AS s ON s.stats_id = i.index_id AND s.[object_id] = i.[object_id]
WHERE k.TYPE = 'PK'
) pk ON o.[parent_object_id] = pk.table_id
WHERE type_desc IN ('PRIMARY_KEY_CONSTRAINT') 
and 'importTable({sourceTable: "'+OBJECT_NAME(parent_object_id)+'",destCollection: "'+OBJECT_NAME(parent_object_id)+'",select: "*",sortField:"'+pk.[column]+'",sortOrder:"'+CASE WHEN pk.is_desc = 0 THEN 'ASC' ELSE 'DESC' END+'",fieldMap: {}});' is not null
ORDER BY OBJECT_NAME(parent_object_id) asc
GO

/*NoSQLBooster for MongoDB uses knexjs to query DBMS 
knexjs is a query builder for PostgreSQL, MySQL and SQLite3, designed to be flexible, portable, and fun to use. http://knexjs.org     
*/ 

//There's probably an easier way to do this but I want a empty database 
use('sqlDbName') 
db.dropDatabase() 
use('sqlDbName') 

const BATCH_SIZE = 1000; 

let knex = require('knex')({ 
    client: "mssql", 
    connection: { 
        "database": "sqlDbName", 
        "server": "localhost", 
        "user": "sqlUser", 
        "password": "sqlPassword", 
        "options": { 
            "entryped": true 
        } 
    } 
}); 

function importTable({ sourceTable, destCollection, select, sortField, sortOrder, fieldMap }) { 
    console.log(`import table "${sourceTable}" to collection ${destCollection} begin ...`); 

    let offset = 0; 
    let continueRead = true; 

    while (continueRead) { 
        //The knex.raw may also be used to build a full query and execute it, as a standard query builder query would be executed. 
        //let srcData = await(knex.raw('select * from users where id = ?', [1])) 
        let srcData = await(knex.select(select.split(',')) 
            //.where('votes', '>', 100)   //add where clauses, refer to http://knexjs.org/#Builder-where  
            .from(sourceTable) 
            .orderBy(sortField, sortOrder) 
            .offset(offset) 
            .limit(BATCH_SIZE)); 

        let readLength = srcData.length; 

        offset = offset + readLength; 

        if (readLength < BATCH_SIZE) 
            continueRead = false; 

        readLength && db.getCollection(destCollection).insertMany(srcData.map((record) => { 
            let dst = {}; 

            for (let key in record) { 
                let val = _.isInteger(record[key]) ? NumberInt(`${record[key]}`) : record[key]; 
                dst
                

We have worked out a test build to resolve the issue, please download and give it a try.

Linux: https://nosqlbooster.com/s3/download/releasesv4/nosqlbooster4mongo-4.5.3.AppImage

Works like a charm, Thank you