pre-populate sql data using recursion on each combination

There are 2 problems in query

  1. cte does not exist in first Recursive Block.
  2. Place the Insert INTO statement before recursive block

So your final query should be like below

insert into counter_key

WITH RECURSIVE
cte AS ( SELECT 1 number 
            UNION ALL
            SELECT number + 1 FROM cte WHERE number < 2 ),
platform AS ( SELECT 'DG' platform
              UNION ALL
              SELECT 'NK' ),
department AS ( SELECT 'KK' department
                UNION ALL
                SELECT 'TG'
                UNION ALL
                SELECT 'LO'
                UNION ALL
                SELECT 'NP'
                UNION ALL
                SELECT 'UI'
                UNION ALL
                SELECT 'BG'
                UNION ALL
                SELECT 'ED'
                UNION ALL
                SELECT 'CC' )

SELECT null, number, CONCAT_WS('_', platform, department), 1
FROM department
CROSS JOIN platform
CROSS JOIN cte 
order by 3,2;

DEMO

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top