Node.js使用mssql包向SQL Server批量插入一对多关系数据的实现方法

你贴的示例代码无法直接实现需求，存在不少语法和逻辑错误，同时由于会员表ID是自增主键，必须先插入会员记录拿到对应ID后才能插入关联的消费记录。下面是正确的实现方案：

原代码核心问题 #

• 变量名混乱：定义了membertable后误用table变量加列，定义consumeTable后也误用table变量操作，会直接报错
• 字段类型错误：name是字符串类型不应使用sql.Int，registerDate和消费记录的date字段应该用sql.DateTime类型存储，不要存为字符串
• 逻辑错误：会员未插入数据库的情况下无法提前拿到自增ID，无法提前填充consumeTable的MemberId字段
• 性能问题：循环内调用bulk方法违背了批量插入的设计初衷，会产生大量不必要的数据库请求

方案1：事务+逐行插入（适合数据量较小的场景） #

用事务保证数据一致性，插入每一条会员记录后立即获取自增ID，填充对应消费记录的MemberId，最后批量插入所有消费记录。

const sql = require('mssql')

// 数据库连接配置
const config = {
  user: '你的数据库用户名',
  password: '你的数据库密码',
  server: '数据库地址',
  database: '数据库名',
  options: {
    trustServerCertificate: true, // 本地开发用，生产环境按需配置
  }
}

// 待插入的原始数据
const rawData = [/* 你的JSON格式数据 */]

async function bulkInsert() {
  let transaction
  try {
    // 连接数据库
    await sql.connect(config)
    // 开启事务
    transaction = new sql.Transaction()
    await transaction.begin()

    // 准备消费记录表的批量结构
    const consumeTable = new sql.Table('ConsumeRecord')
    consumeTable.columns.add('MemberId', sql.Int, {nullable: false})
    consumeTable.columns.add('Date', sql.DateTime, {nullable: false})
    consumeTable.columns.add('price', sql.Money, {nullable: false})

    for (const member of rawData) {
      // 插入单条会员记录，同时获取自增ID
      const insertMemberRes = await transaction.request()
        .input('name', sql.VarChar(50), member.name)
        .input('registerDate', sql.DateTime, member.registerDate)
        .input('gender', sql.Int, member.gender)
        .query(`INSERT INTO Member (name, registerDate, gender)
                OUTPUT INSERTED.id
                VALUES (@name, @registerDate, @gender)`)
      
      const memberId = insertMemberRes.recordset[0].id
      // 填充当前会员的消费记录到批量表
      member.consumeRecord.forEach(record => {
        consumeTable.rows.add(memberId, record.date, record.price)
      })
    }

    // 批量插入所有消费记录
    await transaction.request().bulk(consumeTable)
    // 提交事务
    await transaction.commit()
    console.log('所有数据插入成功')
  } catch (err) {
    // 出错回滚事务
    if (transaction) await transaction.rollback()
    console.error('插入失败，已回滚:', err)
  } finally {
    // 关闭连接
    await sql.close()
  }
}

bulkInsert()

方案2：批量插入会员后匹配ID（适合数据量大的场景） #

如果会员数据量很大，逐行插入性能低，可以先给所有会员加一个临时唯一标识（比如可以用name+registerDate的组合，只要能唯一区分会员即可），批量插入所有会员后一次性查询所有会员的ID，匹配后再批量插入消费记录，全程只需要3次数据库请求，性能更高。

注意事项 #

• 两种方案都使用了事务，保证会员和消费记录的数据一致性，不会出现半截数据
• 所有日期字段都用了DateTime类型存储，避免字符串格式不兼容的问题
• 消费记录统一用bulk批量插入，大大减少数据库请求次数，性能远高于循环单条插入

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