SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 @json nvarchar(max) = N'
[
    {
        "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
            }
        ]
    }
]';

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];
6 rows affected

SELECT * FROM Member;
SELECT * FROM ConsumeRecord


Id
name
registerDate
gender


1
Tom
2021-10-10 00:00:00.000
0

2
Mary
2021-06-10 00:00:00.000
1




MemberId
Date
price


1
2021-10-11 00:00:00.000
102.50

1
2021-10-12 00:00:00.000
200.00

2
2021-07-11 00:00:00.000
702.50

2
2021-12-12 00:00:00.000
98.20

MemberId	Date	price
1	2021-10-11 00:00:00.000	102.50
1	2021-10-12 00:00:00.000	200.00
2	2021-07-11 00:00:00.000	702.50
2	2021-12-12 00:00:00.000	98.20