Alternate approach to WITH CTE and large UNION query

user_6848083 0
Viewed 195

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.

sql-insert tsql sql-server sql database
3 Answers

You can use cross apply to duplicate the rows:

insert into [destination_table]  (id, name)
select x.*
from source_table s
cross apply (values (id, 'Red'), (id, 'Green')) x(id, name)
where name = 'Red and Green'
user_106767160

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.

user_142042220

You could use string_split to split the values apart. First replace the ' and ' with a pipe '|'. Then do a string split on the vertical pipe.

drop table if exists #tTEST;
go
select * INTO #tTEST from (values 
(1, '[123]', 'Name', '[Red and Green]')) V(ID, testCol, nameCol, stringCol);

select ID, testCol, nameCol, 
       case when left([value], 1)!='[' then concat('[',[value]) else 
        case when right([value], 1)!=']' then concat([value], ']') else [value] end end valCol
from #tTEST t
     cross apply string_split(replace(t.stringCol, ' and ', '|'), '|');

Results

ID  testCol nameCol valCol
1   [123]   Name    [Red]
1   [123]   Name    [Green]
user_67487580
Related