SQL Server 2019

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

create table Main (
    Id int identity(1, 1),
    Name nvarchar(50)
);
create table Detail (
    MainId int,
    Name nvarchar(50)
);
insert into Main (Name) values ('X');
1 rows affected

declare @MainList nvarchar(1000) = 'A,B,C';
declare @DetailList nvarchar(1000) = 'A2,B2,C2';

declare @IdList table (
    Seq int identity(1, 1),
    Id int
)

insert into Main (Name)
output inserted.Id into @IdList
select value from string_split(@MainList, ',');

insert into Detail (MainId, Name)
select m.Id MainId, d.value Name
from   @IdList m
       inner join
       (select row_number() over (order by (select 1)) Seq, value
       from    string_split(@DetailList, ',')
       ) d
       on m.Seq = d.Seq;

6 rows affected

select * from Main;
select * from Detail;


Id
Name


1
X

2
A

3
B

4
C




MainId
Name


2
A2

3
B2

4
C2