从.json读取sql server表时插入字典表

u2nhd7ah 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(267)

我需要将.json文件解析到SQLServer数据库。我有3张table：预订（商店预订，一次预订可以有几个房间）、房间（商店房间）、房间预订历史（预订和房间之间的商店关联）。下面是一个.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并将其插入到booking table bookings和room table rooms from.json中。在解析过程中，我需要插入roombookinghistory记录，并使用在写入from.json期间生成的id将每个预订与文件中的rooms相关联。

RoomBookingHistory
    RoomBookingId   int             not null     identity(1,1),
    RoomId          int,
    BookingId       int

如何编写sql将正确的关联id插入roombookinghistory？
以下是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

sql JSON sql-server stored-procedures

来源：https://stackoverflow.com/questions/62832115/insert-into-dictionary-table-when-reading-from-json-to-sql-server-tables

1条答案

按热度按时间

wnrlj8wa1#

你可以利用 MERGE INTO... OUTPUT INTO... 语法，它不仅允许您捕获插入的表列（如 INSERT INTO... OUTPUT INTO... )但表源中的其他列（与 INSERT INTO... OUTPUT INTO... ).
在下面的代码中：
在第一个merge语句中，我使用@table变量捕获dbo.booking.bookingid以及相关房间的json blob。
在第二个merge语句中，将bookingid（从第一次合并中捕获）和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)

赞(0）回复(0）举报 2021-07-24

我来回答

从.json读取sql server表时插入字典表

1条答案

相关问题

热门标签

最新问答