NodeJs:一对多批量插入SQL Server

Question

我想使用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	Tom	2021-10-10 00:00:00
2	玛丽	2021-06-10 00:00:00	1

实现这一点的最佳方法是将整件事批量上传到SQL Server，并确保它插入正确的外键。

你有两个选项

• 选项1

  • 将主表上载为表值参数或JSON blob
  • 使用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];

db<gt；小提琴

不幸的是，INSERT只允许您从inserted表中访问OUTPUT，而不允许从任何未插入的列中访问。所以我们需要用一个奇怪的MERGE破解它

如果您能够更改数据库模式并添加uniqueidentifier列，这将大大简化事情，因为您可以在客户端生成GUID，然后直接将它们插入数据库。

uniqueidentifier列作为非PK列

该列被用作"列"；临时的"；键，以帮助客户端应用程序检索插入记录的identity值。

在您的情况下，一旦将数据插入到第一个表中，就可以运行查询以获取基于uniqueidentifier的PK，并填充第二个表的FK列。

uniqueidentifier列作为PK列

使用uniqueidentifier类型列作为数据库中的PK/FK。您可以轻松导入数据层次结构，而无需使用任何巫毒魔法来处理identity值。