Add a NewID using several columns to define distinct records 1

[SDB15]

I have imported some data from excel spreadsheets where a person may pop up multiple times over the years of data I am working with. I want to assign an ID to them by using newid. How do I insert the new column and get SQL to assign the same ID to the same person even though there are different occurances?

 

[markros]

Using SQL Server 2005 and up is very easy

select * into myNewTable from (select *, row_Number() over (partition by PersonName) as IdField from myCurrentTable) X

 

[SDB15]

That makes sense, thanks! Now how do I add more than one criteria to the partition?

 

[markros]

Just separate them with command, e.g.

partition by PersonName, PersonAddress1, etc.

 

[markros]

Comma, not command - sorry, I'm almost ready to leave now.

 

[SDB15]

Got it. Thanks again for your help.

 

[SDB15]

Ran into some problems here is my code:

select * into Republicans_2 from (select *, row_Number() 
over (partition by 
LName,
FName,
Addr1,
City,
Zip) 
as IdField from Republicans) X

I get this message: "The ranking function "row_Number" must have an ORDER BY clause."

And where do I specify the newid command? Will this create a string of characters like

CAST(Replace(NewID(),'-','') AS Varchar(32))

?

 

[bborissov]

select Republicans.*, Tbl1.New_Id
       into Republicans_2
from Republicans
INNER JOIN (SELECT LName,
                   FName,
                   Addr1,
                   City,
                   Zip,
                   MAX(CAST(Replace(NewID(),'-','') AS Varchar(32))) AS New_Id
            FROM Republicans
            GROUP BY LName, FName, Addr1, City, Zip) Tbl1
ON Republicans.LName = Tbl1.LName
   Republicans.FName = Tbl1.FName
   Republicans.Addr1 = Tbl1.Addr1
   Republicans.City  = Tbl1.City
   Republicans.Zip   = Tbl1.Zip

NOT TESTED!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

You can add ORDER BY any column into the OVER (...) clause.

If you want your newID to be a GUID field and not an integer, I guess you can add an extra field to your table
and then to UPDATE using self-join.