NodeJS使用mssql操作SQL Server批量插入报错如何解决？

错误原因 #

你使用的INSERT INTO LOGIN (username, clave) VALUES ?是MySQL专属的批量传参语法，SQL Server 不支持该占位符用法，因此触发语法报错。

可用实现方案 #

方案1：表值参数（TVP，推荐） #

这是SQL Server官方支持的批量插入方案，性能最优，也能避免SQL注入风险。
首先需要先在你的SQL Server数据库中执行一次语句创建自定义表类型：

CREATE TYPE LoginTableType AS TABLE (
    username VARCHAR(100),
    clave VARCHAR(100)
)

然后Node.js代码调整如下：

const sql = require('mssql');
const { database } = require('../keys');  

sql.connect(database, async function (err, con) {
    if (err) throw err;
    console.log("Connected!");
    
    // 构造表值参数
    const tvp = new sql.Table()
    // 列定义要和刚才创建的表类型对应
    tvp.columns.add('username', sql.VarChar(100))
    tvp.columns.add('clave', sql.VarChar(100))
    
    // 批量添加行数据
    const values = [
        ['John', 'Highway 71'],
        ['Peter', 'Lowstreet 4'],
        ['Amy', 'Apple st 652'],
        ['Hannah', 'Mountain 21'],
        ['Michael', 'Valley 345'],
        ['Sandy', 'Ocean blvd 2'],
        ['Betty', 'Green Grass 1'],
        ['Richard', 'Sky st 331'],
        ['Susan', 'One way 98'],
        ['Vicky', 'Yellow Garden 2'],
        ['Ben', 'Park Lane 38'],
        ['William', 'Central st 954'],
        ['Chuck', 'Main Road 989'],
        ['Viola', 'Sideway 1633']
    ]
    values.forEach(row => tvp.rows.add(...row))

    // 执行插入语句
    const request = new sql.Request()
    request.input('LoginData', tvp)
    const result = await request.query(`
        INSERT INTO LOGIN (username, clave)
        SELECT username, clave FROM @LoginData
    `)
    
    console.log("Number of records inserted: " + result.rowsAffected[0])
})

方案2：拼接VALUES子句（适合小数据量） #

如果数据量不大（小于100条），可以直接拼接多组VALUES的写法，SQL Server支持INSERT INTO 表(列) VALUES (v1,v2),(v3,v4)...的语法：

const sql = require('mssql');
const { database } = require('../keys');  

sql.connect(database, async function (err, con) {
    if (err) throw err;
    console.log("Connected!");
    
    const values = [
        ['John', 'Highway 71'],
        ['Peter', 'Lowstreet 4'],
        // 剩余的业务数据
    ]
    // 生成占位符和参数列表
    const params = []
    const valuePlaceholders = values.map((row, idx) => {
        const userPlaceholder = `@username${idx}`
        const clavePlaceholder = `@clave${idx}`
        params.push({name: userPlaceholder, type: sql.VarChar(100), value: row[0]})
        params.push({name: clavePlaceholder, type: sql.VarChar(100), value: row[1]})
        return `(${userPlaceholder}, ${clavePlaceholder})`
    }).join(',')

    const request = new sql.Request()
    params.forEach(p => request.input(p.name, p.type, p.value))
    const result = await request.query(`
        INSERT INTO LOGIN (username, clave) VALUES ${valuePlaceholders}
    `)
    console.log("Number of records inserted: " + result.rowsAffected[0])
})

方案3：Bulk Insert（适合超大数据量） #

如果需要插入上万条甚至更多数据，可以用mssql的bulk接口，性能更高：

const sql = require('mssql');
const { database } = require('../keys');  

sql.connect(database, async function (err, con) {
    if (err) throw err;
    console.log("Connected!");
    
    const bulk = new sql.Table('LOGIN')
    // 对应表的列，注意如果有自增主键要设置{ identity: true }
    bulk.columns.add('username', sql.VarChar(100), { nullable: false })
    bulk.columns.add('clave', sql.VarChar(100), { nullable: false })
    
    const values = [
        // 你的业务数据
    ]
    values.forEach(row => bulk.rows.add(...row))

    const result = await sql.bulk(bulk)
    console.log("Number of records inserted: " + result.rowsAffected)
})

内容的提问来源于stack exchange，提问作者Juan Castillo