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[fieldMap[key] || key] = val;
            }

            return dst
        }))

        //sleep(100)

        console.log(`table "${sourceTable}"	${offset.toLocaleString()}	 records imported`);
    }

    console.log(`import table "${sourceTable}" to collection ${destCollection} end ...`);
}

/* 
Insert results from getIndexedTablesForMongScript.sql 
*/

//importTable({sourceTable: "SqlTableName",destCollection: "SqlTableName",select: "*",sortField:"ID",sortOrder:"ASC",fieldMap: {}});

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