>>Hi,
>>
>>I've been given a spreadsheet to import into a database and need to normalise the data a bit. The source data when imported looks like this:
>>
>>
Group Code description
>>AAA 001 Desc 1
>>AAA 002 Desc 2
>>BBB 003 Desc 3
>>BBB 004 Desc 4
>>
>>(Of course the actual data makes more sense than those values I've put in.)
>>
>>I need to separate the Groups out into one table with a primary key and and the Code, Description and a Foreign Key value (pointing back to the Group).
>>
>>I use uniqueidentifiers as keys so was trying this:
>>
>>INSERT INTO BenefitCodeGroups
>>SELECT DISTINCT NEWID(), Group
>>FROM BenefitCodesImported
>>
>>but of course that doesn't work as I'll get a record for each distinct combination of NEWID() and Group.
>>
>>Who can help wake my brain up on a Monday morning?
>
>Try:
>
>;with cte as (select [Group] from BenefitCodeGroups GROUP BY [Group])
>
>insert into NewGroups ([Group])
>select [Group] from cte
>
>------------
>Set up default for ID field to be newsequentialID() if you want to use GUID for the PK (IMHO, overkill).
Thanks. I use GUID as the keys because I have to merge data from remote sites. My framework also handles the default values so that I don't have to insert a new record then requery the database to get the new primary key, it generates the primary key locally.