SQL Server 2022

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

create table RoleMembership(id int,username varchar(100),RoleId int)

insert into RoleMembership values 

 ( 1  ,'bobby'    , 1 )
,( 2  ,'bobby'    , 2 )
,( 3  ,'bobby'    , 3 )
,( 4  ,'suzie'    , 3 )
,( 5  ,'suzie'    , 4 )
,( 6  ,'suzie'    , 5 )
,( 7  ,'billy'    , 1 )
,( 8  ,'billy'    , 7 )

 
CREATE TABLE [dbo].[Usermember](
	[Id] [bigint] NOT NULL,
	[Username] [varchar](256) NOT NULL,
	[CreatedAt] [datetime] NOT NULL
)
8 rows affected

INSERT INTO dbo.Usermember (Id, Username, CreatedAt)
select ROW_NUMBER() OVER ( ORDER BY id),Username,getDate()  from (
select   Username,id
,ROW_NUMBER() OVER (partition by Username  ORDER BY Username) rw
from  RoleMembership
)a
where a.rw=1
3 rows affected

select * from  dbo.Usermember 


Id
Username
CreatedAt


1
bobby
2023-05-13 18:52:18.610

2
suzie
2023-05-13 18:52:18.610

3
billy
2023-05-13 18:52:18.610

Id	Username	CreatedAt
1	bobby	2023-05-13 18:52:18.610
2	suzie	2023-05-13 18:52:18.610
3	billy	2023-05-13 18:52:18.610