Search code examples
mysqlsqlpostgresqlcountrecursive-query

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

Solution

  • 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.