CODEWITHSUNDEEP
sqlt-sqlssisduplicatesidentifier
HenryFramer
HenryFramer

Reputation: 33

Create uniqe ID for table and apply it to data with duplicates

I am importing an Excel sheet into SQL Server. It has sales data with client names, no unique client ID in the Excel. I need to create a client ID and assign it to all the rows. Of course there are multiple rows for some clients so they must receive the correct ID (already generated).

I am doing this work in SSIS, is there a node that can create a unique ID when necessary or apply an already created ID when the same client is being processed?

I could also do it with a SQL script in a execute script node. Same task... create a unique id for the clients in the table and apply it to the whole table knowing that there are multiple rows for some clients.

Upvotes: 1

Views: 52

Answers (1)

GMB
GMB

Reputation: 222482

In SQL, one option uses dense_rank(). The following expression gives you a unique, incrementing id for each client:

dense_rank() over(order by client_name) as client_id

If you want a TSQL update statement that sets column client_id:

with cte as (
    select client_id, dense_rank() over(order by client_name) as new_client_id
    from mytable
)
update cte set client_id = new_client_id

Upvotes: 2

Related Questions