node.js - NodeJs:批量插入到 SQL Server 一对多
问题描述
我想使用 nodejs mssql 包批量插入具有以下 json 的数据:
[
{
"name": "Tom",
"registerDate": "2021-10-10 00:00:00",
"gender": 0,
"consumeRecord":[
{
"date": "2021-10-11 00:00:00",
"price": 102.5
},
{
"date": "2021-10-12 00:00:00",
"price": 200
}
]
},
{
"name": "Mary",
"registerDate": "2021-06-10 00:00:00",
"gender": 1,
"consumeRecord":[
{
"date": "2021-07-11 00:00:00",
"price": 702.5
},
{
"date": "2021-12-12 00:00:00",
"price": 98.2
}
]
}
]
我正在尝试对具有多个消费数据的成员记录进行 mssql 批量插入?
有没有什么可以插入一对多的批量插入,如下所示。因为它似乎需要先插入成员表并获取 id(主键)。然后将id(主键)用于消费表关系数据
const sql = require('mssql')
// member table
const membertable = new sql.Table('Member')
table.columns.add('name', sql.Int, {nullable: false})
table.columns.add('registerDate', sql.VarChar(50), {nullable: false})
table.columns.add('gender', sql.VarChar(50), {nullable: false})
// consume record table
const consumeTable = new sql.Table('ConsumeRecord')
table.columns.add('MemberId', sql.Int, {nullable: false})
table.columns.add('Date', sql.VarChar(50), {nullable: false})
table.columns.add('price', sql.Money, {nullable: false})
// insert into member table
jsonList.forEach(data => {
table.rows.add(data.name)
table.rows.add(data.registerDate)
table.rows.add(data.gender)
consumeTable.rows.add(data.memberId) // <---- should insert member table id
consumeTable.rows.add(data.consumeRecord.data)
consumeTable.rows.add(data.consumeRecord.price)
const request = new sql.Request()
request.bulk(consumeTable , (err, result) => {
})
})
const request = new sql.Request()
request.bulk(membertable , (err, result) => {
})
预期记录:成员表
id(自动递增) | 姓名 | 注册日期 | 性别 |
---|---|---|---|
1 | 汤姆 | 2021-10-10 00:00:00 | 0 |
2 | 玛丽 | 2021-06-10 00:00:00 | 1 |
消费记录表
ID | 会员ID | 日期 | 价格 |
---|---|---|---|
1 | 1 | 2021-10-10 00:00:00 | 102.5 |
2 | 1 | 2021-10-12 00:00:00 | 200 |
3 | 2 | 2021-07-11 00:00:00 | 702.5 |
4 | 2 | 2021-12-12 00:00:00 | 98.2 |
解决方案
最好的方法是将整个内容批量上传到 SQL Server,并确保它插入正确的外键。
你有两个选择
选项1
- 将主表作为表值参数或 JSON blob 上传
- 插入 with
OUTPUT
子句将插入的 ID 选择回客户端 - 将这些 ID 关联回子表数据
- 也可以批量插入
选项 2 更简单一些:用 SQL 做所有事情
- 将所有内容上传为一个大的 JSON blob
- 将带有
OUTPUT
子句的主表插入表变量 - 插入子表,加入表变量中的 ID
CREATE TABLE Member(
Id int IDENTITY PRIMARY KEY,
name varchar(50),
registerDate datetime NOT NULL,
gender tinyint NOT NULL
);
CREATE TABLE ConsumeRecord(
MemberId Int NOT NULL REFERENCES Member (Id),
Date datetime not null,
price decimal(9,2)
);
注意列的更合理的数据类型
DECLARE @ids TABLE (jsonIndex nvarchar(5) COLLATE Latin1_General_BIN2 not null, memberId int not null);
WITH Source AS (
SELECT
j1.[key],
j2.*
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON(j1.value)
WITH (
name varchar(50),
registerDate datetime,
gender tinyint
) j2
)
MERGE Member m
USING Source s
ON 1=0 -- never match
WHEN NOT MATCHED THEN
INSERT (name, registerDate, gender)
VALUES (s.name, s.registerDate, s.gender)
OUTPUT s.[key], inserted.ID
INTO @ids(jsonIndex, memberId);
INSERT ConsumeRecord (MemberId, Date, price)
SELECT
i.memberId,
j2.date,
j2.price
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON(j1.value, '$.consumeRecord')
WITH (
date datetime,
price decimal(9,2)
) j2
JOIN @ids i ON i.jsonIndex = j1.[key];
不幸的是,INSERT
只允许您OUTPUT
从inserted
表中,而不是从任何未插入的列中。所以我们需要用一个奇怪的方法破解它MERGE