Skip to content
Advertisement

insert same record multiple times based on the given count

TYPE   COUNT
---     --
ABC     3
EDC     4
FDC     2

I want to insert ABC thrice, EDC four times, FDC twice in a table using single SQL, is it possible? The output of the query should insert into the following table with the following entries.

TYPE
----
ABC
ABC
ABC
EDC
EDC
EDC
EDC
FDC
FDC
FDC 

Thanks

Advertisement

Answer

You would typically use a recursive query:

with recursive cte as (
    select type, cnt from mytable t
    union all
    select type, cnt - 1 from cte where cnt > 1
)
select type from cte

Here is a demo; the syntax works in both Postgres and MySQL 8.0.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement