Thanks, I have never used temp tables in sql server, I'll do some research into it.
>Then do it into a temp table. Concept's the same.
>
>>That sounds disgusting :) I've never heard that word used like this before, LOL.
>>
>>I'm doing this in SQL Server Management Studio, not in VFP so I don't think I can use INTO CURSOR.
>>
>>
>>>Just kinda spitballing here - if you want a unique ID per group, I'd prolly do this:
>>>
>>>
>>>Select group, count(*) from BenefitCodeImported group by 1 into cursor CntGroups
>>>
>>>INSERT INTO BenefitCodeGroups
>>>SELECT DISTINCT NEWID(), Group
>>>FROM CntGroups
>>>
>>>
>>>
>>>>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?