Refactoring a delimited column into a one-to-many table lookup

·

1 min read

Note: MS SQL 2016 and above only solution below.

Dealing with a table where someone's placed multiple string values into a single varchar(max) field.

Something like this:

OurData

Id       Breakdown
1        Boop
2        Scooby
3        Boop:Scooby:Shewop
4        Scooby:Boop

and on it goes. The lookup table is defined like so:

Lookup

PK Name varchar(50) not null
SortOrder int not null

Not sure why a PK is on a varchar(50) but that's what I'm working with. We'll be excising Breakdown on the table and getting rid of it to create a many-to-many relationship between OurData and Lookup

First step is to make sure all possible values from OurData.Breakdown are in our lookup table. Once that's done we create a table OurData_Lookup defined:

OurData_Lookup

PK Id int not null identity
FK OurDataId int
FK LookupName varchar(50)

Now we need to transfer the data over, and why this post exists as it uses some SQL I haven't used before. Doesn't happen very often :)

INSERT INTO OurData_Lookup (OurDataId, LookupName)
SELECT Id, VALUE LookupName
FROM OurData CROSS APPLY string_split(OurData.Breakdown, ':')

Pretty darn slick if you ask me. From there we can remove the Breakdown column from OurData and blamo, refactor is complete