CODEWITHSUNDEEP
sql-server
PsychoDUCK
PsychoDUCK

Reputation: 1103

MS SQL Server - create unique key for user

I have an employee table with multiple entries for the same person. Using their SIN number you are able to determine if they are the same person.

Example:

Id  Name    SIN
1   John Smith  1234
2   John Smith  1234
3   Jess Jones  4321

I want to be able to copy everyone in this table, into a new table. I want to create a new column (UserKey [GUID]) that is unique to the user who is in the table multiple times. I want to use this new UserKey [GUID] instead of the SIN number.

Example:
Id  Name        UserKey (Guid)
1   John Smith  1234DFKJ2328LJFD
2   John Smith  1234DFKJ2328LJFD
3   Jess Jones  9543SLDFJ28EKJFF

I have no idea on how to approach this query. Any help would be great.

I am using MS SQL Server 2008.

Thanks.

Upvotes: 0

Views: 365

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

You can just create a temp table, mapping SIN to a new GUID. Then you can join the original table with the mapping table, and create the new table from that.

# Create a table called #temp with the mappings SID => new GUID
SELECT SIN, newid() UserKey INTO #temp FROM (SELECT DISTINCT SIN FROM Table1) a

# ...and join that and the original table to a new table.
SELECT id, name, userkey
INTO NewTable
FROM Table1 t1
JOIN #temp t2
ON t1.SIN = t2.SIN

SQLFiddle here.

Upvotes: 3

Related Questions