SQL Server 2019

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

CREATE TABLE Table1 (
	ID	INTEGER NOT NULL IDENTITY PRIMARY KEY,
	Ref_No	VARCHAR(10),
	MsgCreatedDt	DATETIME,
	Remarks	VARCHAR(10)
);

INSERT INTO Table1 (Ref_No,MsgCreatedDt,Remarks) VALUES 
 ('Ref1','2020-03-22 17:39:00',NULL),
 ('Ref1','2020-03-22 17:42:00',NULL),
 ('Ref1','2020-03-22 18:10:00',NULL),
 ('Ref2','2020-03-22 16:11:00',NULL),
 ('Ref2','2020-03-22 16:11:00',NULL);

CREATE TABLE Table2 (
	ID	INTEGER NOT NULL IDENTITY PRIMARY KEY,
	Ref_No	VARCHAR(10),
	MsgCreatedDt	DATETIME,
	Remarks	VARCHAR(10)
);
5 rows affected

insert into Table2 (ref_no, msgcreateddt, remarks)
select t.ref_no, t.msgcreateddt,
  case when t.rn1 > 1 then 'Duplicate' end
from (  
  select *,
    row_number() over (partition by ref_no order by msgcreateddt) rn1,
    row_number() over (partition by ref_no, msgcreateddt order by (select null)) rn2
  from Table1
) t  
where t.rn2 = 1
4 rows affected

select * from Table2


ID
Ref_No
MsgCreatedDt
Remarks


1
Ref1
2020-03-22 17:39:00.000
null

2
Ref1
2020-03-22 17:42:00.000
Duplicate

3
Ref1
2020-03-22 18:10:00.000
Duplicate

4
Ref2
2020-03-22 16:11:00.000
null

ID	Ref_No	MsgCreatedDt	Remarks
1	Ref1	2020-03-22 17:39:00.000	null
2	Ref1	2020-03-22 17:42:00.000	Duplicate
3	Ref1	2020-03-22 18:10:00.000	Duplicate
4	Ref2	2020-03-22 16:11:00.000	null