SQL Server 2014

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

CREATE TABLE STORE2
    (ID INT, REPEAT_CUSTOMER_ID VARCHAR(4), STORE VARCHAR(1), DATE DATETIME);
 
INSERT INTO STORE2
    (ID, REPEAT_CUSTOMER_ID, STORE, DATE)
VALUES
    (1, NULL, 'B', '2022-07-19 00:00:00'),
    (2, NULL, 'B', '2022-07-19 00:00:00'),
    (3, '1', 'B', '2022-07-19 00:00:00'),
    (4, NULL, 'B', '2022-07-19 00:00:00');
4 rows affected

CREATE TABLE STORE1
    (ID INT, REPEAT_CUSTOMER_ID VARCHAR(4), STORE VARCHAR(1), DATE DATETIME)
;
INSERT INTO STORE1
    (ID, REPEAT_CUSTOMER_ID, STORE, DATE)
VALUES
    (1, NULL, 'A', '2022-07-19 00:00:00'),
    (2, NULL, 'A', '2022-07-19 00:00:00'),
    (3, NULL, 'A', '2022-07-19 00:00:00')
;
3 rows affected

with CTE as
(
select * from STORE1 
UNION ALL
select * from STORE2
)

,CTE2 as
(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NEW_ID,t.* from CTE t)

,REPEAT_ID as 
(select NEW_ID,ID,REPEAT_CUSTOMER_ID,STORE from CTE2 where REPEAT_CUSTOMER_ID is not null)

,REPEACT_CUSTOMER_ID as 
(select c.NEW_ID as NEW_REPEAT_CUSTOMER_ID,r.NEW_ID
from REPEAT_ID r
left join CTE2 c
on c.ID=r.REPEAT_CUSTOMER_ID and c.STORE=r.STORE
)

select c.* , n.NEW_REPEAT_CUSTOMER_ID
from CTE2 c
left join REPEACT_CUSTOMER_ID n
on c.NEW_ID=n.NEW_ID


NEW_ID
ID
REPEAT_CUSTOMER_ID
STORE
DATE
NEW_REPEAT_CUSTOMER_ID


1
1
null
A
2022-07-19 00:00:00.000
null

2
2
null
A
2022-07-19 00:00:00.000
null

3
3
null
A
2022-07-19 00:00:00.000
null

4
1
null
B
2022-07-19 00:00:00.000
null

5
2
null
B
2022-07-19 00:00:00.000
null

6
3
1
B
2022-07-19 00:00:00.000
4

7
4
null
B
2022-07-19 00:00:00.000
null

NEW_ID	ID	REPEAT_CUSTOMER_ID	STORE	DATE	NEW_REPEAT_CUSTOMER_ID
1	1	null	A	2022-07-19 00:00:00.000	null
2	2	null	A	2022-07-19 00:00:00.000	null
3	3	null	A	2022-07-19 00:00:00.000	null
4	1	null	B	2022-07-19 00:00:00.000	null
5	2	null	B	2022-07-19 00:00:00.000	null
6	3	1	B	2022-07-19 00:00:00.000	4
7	4	null	B	2022-07-19 00:00:00.000	null