Alternate approach to WITH CTE and large UNION query

I’d like to rework a script I’ve been given.

The way it currently works is via a WITH CTE using a large number of UNIONs.

Current setup

We’re taking one record from a source table, inserting it into a destination table once with [Name] A then inserting it again with [Name] B. Essentially creating multiple rows in the destination, albeit with different [Name].

An example of one transaction would be to take this row from [Source]:

ID [123] Name [Red and Green]

The results of my current set up in the [Destination] is:

ID [123] Name [Red]
ID [123] Name [Green]

Current logic

Here’s a simplified version of the current logic:

WITH CTE
AS  
             
(SELECT ID, 
'Red' AS [Name]
FROM [Source_Table]
WHERE [Name] = 'Red and Green'

UNION ALL
              
SELECT ID, 
'Green' AS [Name] 
FROM [Source_Table]
WHERE [Name] = 'Red and Green')


INSERT INTO [Destination_Table]  
(ID, 
[Name])
SELECT ID, 
[Name]
FROM CTE; 

The reason I’d like to rework this is when we get a new [Name], we have to manually add another portion of code into our (ever increasing) UNION, to make sure it gets picked up.

What I’ve considered

What I was considering was setting up a WHILE LOOP (or CURSOR) running off a control table, where we could store all of the [Names]. However, I’m not sure if this would be the best approach and I’m not too familiar yet with LOOPS/CURSORS. Also, Wouldn’t be too sure of how to stop the loop once all [Name]s had been completed.

Any help much appreciated.

Answer

Introduce a new table called Color_List which just contains one row for each possible color. Then do this:

with cte as
(
    select
        st.ID,
        c.colorname
    from
        Source_Table s
    inner join
        Color_List c
    on
        CHARINDEX(c.colorname, s.[Name]) > 0
)

insert into Destination_Table
(
    ID,
    [Name]
)
select
    ID,
    colorname
from
    cte

The benefit of this method is that you aren’t hard-coding any color names in the query. All the color names (and presumably there can be many more than two) get maintained in the Color_List table.