sql - 从 .json 读取到 sql server 表时插入字典表
问题描述
我需要将 .json 文件解析为 SQL Server 数据库。我有3张表:Booking(店铺预订,一个预订可以有几个房间),Room(店铺房间),RoomBookingHistory(Booking和Room之间的店铺关联)。这是一个 .json 文件示例:
[
{
"BookingName": "Egypt, SES, 3 persons, 14 days",
"Paid": "0",
"Active": "1",
"DateOfBooking": "2020/05/07 19:35:02",
"UserLogin": "Alex222",
"Rooms": [
{
"RoomName": "SESBR23",
"Capacity": "2",
"Price": "120",
"Stars": "3"
},
{
"RoomName": "SESBR21",
"Capacity": "1",
"Price": "60",
"Stars": "4"
}
],
"FlightNumber": "FO23465",
"ManagerLogin": "Manger_Egypt_1"
},
{
"BookingName": "Egypt, Hurgada, 3 persons, 7 days",
"Paid": "0",
"Active": "1",
"DateOfBooking": "2020/05/07 17:35:02",
"UserLogin": "Super_tourist",
"Rooms": [
{
"RoomName": "HGST45",
"Capacity": "3",
"Price": "250",
"Stars": "5"
}
],
"FlightNumber": "HR5665",
"ManagerLogin": "Manger_Egypt_2"
}
]
使用创建的存储过程,我读取 .json 并从 .json 插入 Booking 表 Bookings 和 Room 表 Rooms。在解析期间,我需要插入 RoomBookingHistory 记录,并在从 .json 写入期间使用生成的 id 将每个预订与文件中的房间相关联。
RoomBookingHistory
RoomBookingId int not null identity(1,1),
RoomId int,
BookingId int
如何编写 sql 以插入 RoomBookingHistory 正确的关联 ID?
这是sp的代码:
drop procedure if exists dbo.usp_ImportBookingData
go
create procedure dbo.usp_ImportBookingData
@parametrs nvarchar(max)
as
begin
set nocount on
create table #Booking
(
BookingId int not null identity(1,1),
BookingName varchar(30),
Paid bit,
Active bit,
DateOfBooking datetime,
UserId int,
FlightId int,
ManagerId int,
Rooms nvarchar(max)
)
insert into #Booking
(
BookingName,
Paid,
Active,
DateOfBooking,
Rooms
)
select
b.BookingName,
b.Paid,
b.Active,
CONVERT(DATETIME, b.DateOfBooking),
b.Rooms
from openjson(@parametrs)
with
(
BookingName varchar(30) N'$.BookingName',
Paid bit N'$.Paid',
Active bit N'$.Active',
DateOfBooking datetime N'$.DateOfBooking',
Rooms nvarchar(max) N'$.Rooms' as json
) b
insert into dbo.Booking
(
BookingName,
Paid,
Active,
DateOfBooking
)
select
b.BookingName,
b.Paid,
b.Active,
CONVERT(DATETIME, b.DateOfBooking)
from #Booking b
create table #Rooms
(
RoomId int not null identity(1,1),
RoomName varchar(30),
Capacity int,
Price int,
Stars int,
HotelId int
)
insert into #Rooms
(
RoomName,
Capacity,
Price,
Stars
)
select
br.RoomName,
br.Capacity,
br.Price,
br.Stars
from #Booking b
cross apply openjson(b.Rooms)
with
(
RoomName varchar(30) N'$.RoomName',
Capacity int N'$.Capacity',
Price int N'$.Price',
Stars int N'$.Stars'
) br
insert into dbo.Room
(
RoomName,
Capacity,
Price,
Stars
)
select
br.RoomName,
br.Capacity,
br.Price,
br.Stars
from #Rooms br
解决方案
您可以使用MERGE INTO... OUTPUT INTO...
允许您不仅捕获插入的表列(like INSERT INTO... OUTPUT INTO...
)而且捕获表源中的其他列(不像INSERT INTO... OUTPUT INTO...
)的语法。
在下面的代码中:
- 在第一个 MERGE 语句中,我使用 @table 变量来捕获 dbo.Booking.BookingId 以及关联房间的 JSON blob。
- 在第二个 MERGE 语句中,将 BookingId(从第一个 MERGE 捕获)和 dbo.Room.RoomId 直接输出到 dbo.RoomBookingHistory 表中。
create procedure dbo.usp_ImportBookingData
@parametrs nvarchar(max)
as
begin
set nocount on
declare @InsertedBookings table (
BookingId int not null,
Rooms nvarchar(max)
);
merge into dbo.Booking as Target
using (
select BookingName, Paid, Active, DateOfBooking, Rooms
from openjson(@parametrs) with (
BookingName varchar(30) N'$.BookingName',
Paid bit N'$.Paid',
Active bit N'$.Active',
DateOfBooking datetime N'$.DateOfBooking',
Rooms nvarchar(max) N'$.Rooms' as json
)
) as Source
on (0=1) -- force "not matched" for insert
when not matched then
insert (BookingName, Paid, Active, DateOfBooking)
values (BookingName, Paid, Active, DateOfBooking)
output Inserted.BookingId, Source.Rooms into @InsertedBookings;
merge into dbo.Room as Target
using (
select BookingId, RoomName, Capacity, Price, Stars
from @InsertedBookings
cross apply openjson(Rooms) with (
RoomName varchar(30) N'$.RoomName',
Capacity int N'$.Capacity',
Price int N'$.Price',
Stars int N'$.Stars'
)
) as Source
on (0=1) -- force "not matched" for insert
when not matched then
insert (RoomName, Capacity, Price, Stars)
values (RoomName, Capacity, Price, Stars)
output Inserted.RoomId, Source.BookingId into dbo.RoomBookingHistory (RoomId, BookingId);
end
然后使用它...
declare @parametrs nvarchar(max) = N'[
{
"BookingName": "Egypt, SES, 3 persons, 14 days",
"Paid": "0",
"Active": "1",
"DateOfBooking": "2020/05/07 19:35:02",
"UserLogin": "Alex222",
"Rooms": [
{
"RoomName": "SESBR23",
"Capacity": "2",
"Price": "120",
"Stars": "3"
},
{
"RoomName": "SESBR21",
"Capacity": "1",
"Price": "60",
"Stars": "4"
}
],
"FlightNumber": "FO23465",
"ManagerLogin": "Manger_Egypt_1"
},
{
"BookingName": "Egypt, Hurgada, 3 persons, 7 days",
"Paid": "0",
"Active": "1",
"DateOfBooking": "2020/05/07 17:35:02",
"UserLogin": "Super_tourist",
"Rooms": [
{
"RoomName": "HGST45",
"Capacity": "3",
"Price": "250",
"Stars": "5"
}
],
"FlightNumber": "HR5665",
"ManagerLogin": "Manger_Egypt_2"
}
]';
exec dbo.usp_ImportBookingData @parametrs=@parametrs;
select * from dbo.Booking;
select * from dbo.Room;
select * from dbo.RoomBookingHistory;
产生结果...
BookingId BookingName Paid Active DateOfBooking
----------- ------------------------------ ----- ------ -----------------------
1 Egypt, SES, 3 persons, 14 days 0 1 2020-05-07 19:35:02.000
2 Egypt, Hurgada, 3 persons, 7 d 0 1 2020-05-07 17:35:02.000
(2 rows affected)
RoomId RoomName Capacity Price Stars
----------- ------------------------------ ----------- ----------- -----------
1 SESBR23 2 120 3
2 SESBR21 1 60 4
3 HGST45 3 250 5
(3 rows affected)
RoomBookingId RoomId BookingId
------------- ----------- -----------
1 1 1
2 2 1
3 3 2
(3 rows affected)
推荐阅读
- tableau-api - Tableau 中的默认参数值
- pyspark - 我们可以从 aws Glue PySpark 作业触发 AWS Lambda 函数吗?
- html - 一致的社交媒体按钮
- python - 使用 cx_oracle 在 python 中读取 Clob - 不工作
- postgresql - 无法使用 PostgreSql 在代码优先方法中自动创建用户定义的表
- r - 根据列的名称和另一个列值将 NA 分配给列
- oracle - Oracle Forms 自动在下一行添加员工
- javascript - 如何使用moment js计算人的年龄?
- c# - 如何仅在单击按钮时验证单元格?
- java - JPA EntityManager 无法在 PostInsertEventListener 中刷新