Best practices for normalizing a table and converting a nvarchar column to a foreign key reference

Question

I have a large, un-normalized table in T-SQL. Imagine it has the following structure:

tbl_Item:
ItemID
ItemName
ItemGroup
Price

With data like this:

1 - ItemA - GroupA - $9.99
2 - ItemB - GroupA - $9.99
3 - ItemC - GroupA - $9.99
4 - ItemX - GroupB - $4.00
5 - ItemY - GroupB - $4.00

I want to normalize it into two, entirely new tables:

tbl_Item:
ItemID
ItemName
FK_Group

and:

tbl_Group:
GroupID
GroupName
Price

The problem I have is taking the ItemGroup data from the initial table and replacing it with the corresponding GroupID of the new table. I will populate the tbl_Group table by running:

SELECT DISTINCT ItemGroup FROM tbl_Item

...and inserting all the values into tbl_Group, thus giving them an ID number.

The only way I can see then is to write a script which loops through tbl_Item, queries the ItemGroup column against the new tbl_Group table, and inserts the ID into the new item table's FK_Group column.

Is there a better way to do this?

Answer 1

You can create the table as:

select identity() as GroupId, GroupName, Price 
into tbl_groups
from tbl_item
group by GroupName, Price;

Alternatively, create the table as:

create table tbl_groups (
    GroupId int identity() primary key,
    GroupName varchar(255),
    Price number(10, 2)
);

insert into tbl_groups(GroupName, Price)
    select distinct GroupName, Price
    from tbl_Items;