使用Node mssql包进行大容量插入

const sql = require('mssql');

async function insertData(data) {
    try {
        await sql.connect('mssql://username:password@server/database');
        const request = new sql.Request();
        const ps = request.createBatch();

        data.forEach(row => {
            ps.input('column1', sql.VarChar, row.column1);
            ps.input('column2', sql.Int, row.column2);
            ps.add('INSERT INTO table (column1, column2) VALUES (@column1, @column2)');
        });

        await ps.execute();
    } catch (err) {
        console.error(err);
    } finally {
        await sql.close();
    }
}

async function insertDataWithTransaction(data) {
    try {
        await sql.connect('mssql://username:password@server/database');
        const transaction = new sql.Transaction();
        await transaction.begin();

        const request = new sql.Request(transaction);
        const ps = request.createBatch();

        data.forEach(row => {
            ps.input('column1', sql.VarChar, row.column1);
            ps.input('column2', sql.Int, row.column2);
            ps.add('INSERT INTO table (column1, column2) VALUES (@column1, @column2)');
        });

        await ps.execute();
        await transaction.commit();
    } catch (err) {
        console.error(err);
        await transaction.rollback();
    } finally {
        await sql.close();
    }
}

async function insertDataInBatches(data, batchSize = 1000) {
    try {
        await sql.connect('mssql://username:password@server/database');
        for (let i = 0; i < data.length; i += batchSize) {
            const batchData = data.slice(i, i + batchSize);
            await insertBatch(batchData);
        }
    } catch (err) {
        console.error(err);
    } finally {
        await sql.close();
    }
}

async function insertBatch(batchData) {
    const request = new sql.Request();
    const ps = request.createBatch();

    batchData.forEach(row => {
        ps.input('column1', sql.VarChar, row.column1);
        ps.input('column2', sql.Int, row.column2);
        ps.add('INSERT INTO table (column1, column2) VALUES (@column1, @column2)');
    });

    await ps.execute();
}