SQL Server 2019

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

CREATE TABLE tab1(
    [KEY]                    INT,
    [CLIENT]                 VARCHAR(30),
    [QUANTITY]               INT,
    [CONTRACT_NO]            INT,
    [FC_COUNT]               INT,
    [DELETE_FLAG]            VARCHAR(30),
    [RECORD_UPDATED_DATE]    VARCHAR(30)
);
INSERT INTO tab1 VALUES
(345, 	'A', 	1000, 	5015, 	1, 	'N', 	'31/12/9999'),
(346, 	'B', 	2000, 	9352, 	1, 	'N', 	'31/12/9999'),
(347, 	'C', 	3000, 	6903, 	1, 	'N', 	'31/12/9999'),
(348, 	'D', 	1000, 	7085, 	1, 	'N', 	'31/12/9999'),
(349, 	'E', 	1000, 	8488, 	1, 	'N', 	'31/12/9999'),
(350, 	'F', 	 500, 	6254, 	1, 	'N', 	'31/12/9999');

SELECT * FROM tab1


KEY
CLIENT
QUANTITY
CONTRACT_NO
FC_COUNT
DELETE_FLAG
RECORD_UPDATED_DATE


345
A
1000
5015
1
N
31/12/9999

346
B
2000
9352
1
N
31/12/9999

347
C
3000
6903
1
N
31/12/9999

348
D
1000
7085
1
N
31/12/9999

349
E
1000
8488
1
N
31/12/9999

350
F
500
6254
1
N
31/12/9999


CREATE TABLE tab2(
    [KEY]           INT, 	
    [CLIENT]        VARCHAR(30),
    [QUANTITY]      INT,
    [CONTRACT_NO]   INT,
    [FC_COUNT]      INT
);
INSERT INTO tab2 VALUES
(345, 	'A', 	1299, 	5015, 	1),
(346, 	'B', 	2011, 	9352, 	1),
(351, 	'Z', 	5987, 	5541, 	1);

SELECT * FROM tab2;


KEY
CLIENT
QUANTITY
CONTRACT_NO
FC_COUNT


345
A
1299
5015
1

346
B
2011
9352
1

351
Z
5987
5541
1


SELECT tab1.[KEY],
       tab1.[CLIENT],
       tab1.[QUANTITY],
       tab1.[CONTRACT_NO],
       tab1.[FC_COUNT],
       CASE WHEN tab2.[KEY] IS NOT NULL
            THEN 'N'
            ELSE 'Y'
       END       AS [DELETE_FLAG],
       CAST(GETDATE() AS DATE) AS [RECORD_UPDATED_DATE]
FROM      tab1
LEFT JOIN tab2
       ON tab1.[KEY] = tab2.[KEY] 
UNION ALL 
SELECT *, 
       'N'          AS [DELETE_FLAG],
       '9999-12-31' AS [RECORD_UPDATED_DATE]
FROM tab2
ORDER BY [KEY]


KEY
CLIENT
QUANTITY
CONTRACT_NO
FC_COUNT
DELETE_FLAG
RECORD_UPDATED_DATE


345
A
1000
5015
1
N
2024-04-05

345
A
1299
5015
1
N
9999-12-31

346
B
2011
9352
1
N
9999-12-31

346
B
2000
9352
1
N
2024-04-05

347
C
3000
6903
1
Y
2024-04-05

348
D
1000
7085
1
Y
2024-04-05

349
E
1000
8488
1
Y
2024-04-05

350
F
500
6254
1
Y
2024-04-05

351
Z
5987
5541
1
N
9999-12-31


WITH cte AS (
    SELECT tab1.[KEY],
           tab1.[CLIENT],
           tab1.[QUANTITY],
           tab1.[CONTRACT_NO],
           tab1.[FC_COUNT],
           CASE WHEN tab2.[KEY] IS NOT NULL
                THEN 'N'
                ELSE 'Y'
           END       AS [DELETE_FLAG],
           CAST(GETDATE() AS DATE) AS [RECORD_UPDATED_DATE]
    FROM      tab1
    LEFT JOIN tab2
           ON tab1.[KEY] = tab2.[KEY] 
    UNION ALL 
    SELECT *, 
           'N'          AS [DELETE_FLAG],
           '9999-12-31' AS [RECORD_UPDATED_DATE]
    FROM tab2
)
SELECT *
INTO history
FROM cte
ORDER BY [KEY];

9 rows affected

SELECT * FROM history;


KEY
CLIENT
QUANTITY
CONTRACT_NO
FC_COUNT
DELETE_FLAG
RECORD_UPDATED_DATE


345
A
1000
5015
1
N
2024-04-05

346
B
2000
9352
1
N
2024-04-05

347
C
3000
6903
1
Y
2024-04-05

348
D
1000
7085
1
Y
2024-04-05

349
E
1000
8488
1
Y
2024-04-05

350
F
500
6254
1
Y
2024-04-05

345
A
1299
5015
1
N
9999-12-31

346
B
2011
9352
1
N
9999-12-31

351
Z
5987
5541
1
N
9999-12-31

KEY	CLIENT	QUANTITY	CONTRACT_NO	FC_COUNT	DELETE_FLAG	RECORD_UPDATED_DATE
345	A	1000	5015	1	N	31/12/9999
346	B	2000	9352	1	N	31/12/9999
347	C	3000	6903	1	N	31/12/9999
348	D	1000	7085	1	N	31/12/9999
349	E	1000	8488	1	N	31/12/9999
350	F	500	6254	1	N	31/12/9999

KEY	CLIENT	QUANTITY	CONTRACT_NO	FC_COUNT	DELETE_FLAG	RECORD_UPDATED_DATE
345	A	1000	5015	1	N	2024-04-05
345	A	1299	5015	1	N	9999-12-31
346	B	2011	9352	1	N	9999-12-31
346	B	2000	9352	1	N	2024-04-05
347	C	3000	6903	1	Y	2024-04-05
348	D	1000	7085	1	Y	2024-04-05
349	E	1000	8488	1	Y	2024-04-05
350	F	500	6254	1	Y	2024-04-05
351	Z	5987	5541	1	N	9999-12-31