SQL Server 2017

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

Create table t1 (id [int] IDENTITY(1,1) NOT NULL, test nvarchar(20), question nvarchar(5));
create table t2 (id [int] IDENTITY(1, 1) NOT NULL, t1_ID int, Ans nvarchar(5));
insert into t1 (test, question) VALUES ('Test1', 'Q'), ('Test2', 'Q'), ('Test3', 'Q')
3 rows affected

select * from t1;


id
test
question


1
Test1
Q

2
Test2
Q

3
Test3
Q


insert into t2 (t1_id, ans) VALUES ('1', 'A1'), ('1', 'A2'),('2', 'B1'),('2', 'B2'),('2', 'B3'),('3', 'C1'),('3', 'C2');
7 rows affected

select * from t2;


id
t1_ID
Ans


1
1
A1

2
1
A2

3
2
B1

4
2
B2

5
2
B3

6
3
C1

7
3
C2


declare @ids table (id int);

insert into t1 (test, question)
    output inserted.id into @ids
    select test, 'P' from t1;

 select * from @ids
 
 declare @cnt int;
 select @cnt = count(id) from @ids;
 insert into t2 (t1_id, ans)
    select i.id, t2.ans
    from t2
    left join @ids i on i.id = (t2.t1_id + @cnt)


id


4

5

6


select * from t2


id
t1_ID
Ans


1
1
A1

2
1
A2

3
2
B1

4
2
B2

5
2
B3

6
3
C1

7
3
C2

8
4
A1

9
4
A2

10
5
B1

11
5
B2

12
5
B3

13
6
C1

14
6
C2

id	t1_ID	Ans
1	1	A1
2	1	A2
3	2	B1
4	2	B2
5	2	B3
6	3	C1
7	3	C2
8	4	A1
9	4	A2
10	5	B1
11	5	B2
12	5	B3
13	6	C1
14	6	C2

id	t1_ID	Ans
1	1	A1
2	1	A2
3	2	B1
4	2	B2
5	2	B3
6	3	C1
7	3	C2
8	4	A1
9	4	A2
10	5	B1
11	5	B2
12	5	B3
13	6	C1
14	6	C2

id	t1_ID	Ans
1	1	A1
2	1	A2
3	2	B1
4	2	B2
5	2	B3
6	3	C1
7	3	C2
8	4	A1
9	4	A2
10	5	B1
11	5	B2
12	5	B3
13	6	C1
14	6	C2