Copy distinct values of one column to another table with different structure

Question

Using SQL Server, I have a table RoleMembership:

Id	Username	RoleId
1	bobby	1
2	bobby	2
3	bobby	3
4	suzie	3
5	suzie	4
6	suzie	5
7	billy	1
8	billy	7

etc.

I want to create a new Usermember table with a row for each distinct value for the RoleMembership.Username column, alone with some ID and date columns.

CREATE TABLE [access].[Usermember]
(
    [Id] [bigint] NOT NULL,
    [Username] [varchar](256) NOT NULL,
    [CreatedAt] [datetime] NOT NULL
)

I want to copy the unique usernames such that the table will be as follows:

Id	Username	CreatedAt
1	bobby	05-13-23
2	suzie	05-13-23
3	billy	05-13-23

How can I do an insert into select distinct to copy unique Usernames into a row in this new table?

I've tried something like this:

INSERT INTO access.Usermember (Id, Username, CreatedAt)
    SELECT
        dbo.NextId('Usermember') + ROW_NUMBER() OVER (ORDER BY Id ASC), 
        (SELECT DISTINCT MemberName 
         FROM access.RoleMembership), GETDATE()

But the syntax isn't quite right.

[Id] [bigint] NOT NULL, how many users are you planning to have? INT should be enough for everybody — siggemannen, May 13 '23 at 19:48

score 1 · Accepted Answer · answered May 13 '23 at 19:29

If you define your table with an Identity column and provide a default value for CreatedAt then you simply have to worry about a single column:

CREATE TABLE dbo.Usermember(
    Id bigint NOT NULL identity,
    Username varchar(256) NOT NULL,
    CreatedAt datetime2(3) NOT NULL default(Getdate())
);

insert into dbo.Usermember(Username)
select Username
from RoleMembership
group by Username
order by min(id); /* Assuming you wish to preserve the ordering, although this should not be necessary */

abolfazl sadeghi · Answer 2 · 2023-05-13T22:41:22.433

you can use window function Find distinct and with row_number can generate new id

If you don't want(based on your business) the ID to be increased automatically, can use this row_number outside(this is code), otherwise it is better to be automatic.

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

dbfiddle

Copy distinct values of one column to another table with different structure

2 Answers2