我需要将.json文件解析到SQL Server数据库。我有3张桌子：预订(商店预订，一次预订可以有几个房间(，房间(商店房间(，RoomBookingHistory(预订和房间之间的商店关联(。下面是一个.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将每个Booking与文件中的Rooms关联起来
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...语法，该语法不仅可以捕获插入的表列(如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)