CODEWITHSUNDEEP
sqlsql-server
JohnFP
JohnFP

Reputation: 13

Add entry to a table for each distinct entry in a column

I have a table of items

enter image description here

I want to add a new row for each distinct customerid that adds a new role called say z so for instance the above would then become:

enter image description here

I could do this manually but there are over 750K lines in this table so that would take awhile

I will also hardcode the entries for date and user

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use insert with a query to generate one row per distinct customerid:

insert into items (created, createdby, customerid, role)
    select getdate(), 1, customerid, 'z'
    from items i
    group by customerid;

I'm not sure what you want for createdby, so I just set it to 1.

Upvotes: 1

Related Questions