英文:
Copy distinct values of one column to another table with different structure
问题
INSERT INTO access.Usermember (Id, Username, CreatedAt)
SELECT
dbo.NextId('Usermember') + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
Username, GETDATE()
FROM (SELECT DISTINCT Username FROM access.RoleMembership) AS DistinctUsernames
英文:
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.
答案1
得分: 1
如果您在表中定义了一个标识列并为 CreatedAt 提供了默认值，那么您只需要担心一个列：
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); /* 假设您希望保留顺序，尽管这不应该是必要的 */
英文:
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 */
答案2
得分: -1
你可以使用窗口函数 Find distinct 和配合 row_number 可以生成新的 id
如果你不希望（根据你的业务需求）ID 自动增加，可以在外部使用这个 row_number（这是代码），否则最好是自动的。
``` SQL
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
<details>
<summary>英文:</summary>
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][1]
[1]:https://dbfiddle.uk/7vqonBNi
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论