Inserting duplicate records based on a value without using cursor

Question

I had a problem in database. I have to insert duplicate records of a particular record on a another table based on a value. First i used cursor to fetch each records and get the number of duplication i wants and after that used another cursor for duplication. Everything worked fine. But if the records in more than 500, i went dead slow. Then i did some research and found a way to insert without cursor.

INSERT INTO report(id, Name) 
        SELECT i.id,i.Name FROM (SELECT  1  AS id 
        UNION SELECT  2  
        UNION SELECT 3 
        UNION SELECT 4 
        UNION SELECT 5 
        UNION SELECT 6 
        UNION SELECT 7 
        UNION SELECT 8 
        UNION SELECT 9 
        UNION SELECT 10) AS o  
        INNER  JOIN table  i WHERE o.id<=i.frequence;

where frequence is the number of duplication. Please drop your idea to improve your query.

This query seems pretty cool. Is there a reason you are looking for another way to do it? — Tom, Oct 19 '12 at 14:07
@Tom: Because im inserting thousands of records, so a small optimization may save huge amount of time. Thats the reason. — Vijaychandar, Oct 19 '12 at 14:13

score 1 · Answer 1 · answered Oct 19 '12 at 14:20

You could try creating a table with a record for each value from 1 to 10 and then join to that. I'm not sure it would be any faster though. You would have to experiment with it.

In this example the table with the values from 1 to 10 is called "dup" and the field containing these values is called "id".

INSERT INTO report(id, Name) 

SELECT i.id, i.Name 

FROM   table i 

       JOIN dup d
       ON d.id <= i.frequence;

score 1 · Answer 2 · edited May 23 '17 at 12:04

If you have any table that contains a row number that goes at least as high as the maximum frequence, you could to this:

INSERT INTO report(id, Name) 
        SELECT i.id,i.Name FROM table i 
            inner join (
                select distinct some_row_number_column from some_table
            ) o on o.some_row_number_column <= i.frequence;

This is basically the same as what you were doing, but it avoids the messy union all statements.

Or you could make a cursor that inserts numbers from 1 to the maximum frequence into a temporary table, then use that in your join. Or you could use a row numbering variable to generate the necessary sequence. Basically, do anything that will generate a list of consecutive numbers from 1 to the maximum that you need.

I would normally use recursion for this (DB2 syntax):

INSERT INTO report(id, Name) 
        with num_list (num) as (
             values (1)
                 union all 
             select num + 1 from num_list
                 where num < (select max(frequence) from table)
        )
        SELECT i.id,i.Name FROM table i 
            inner join num_list on num_list.num <= i.frequence;

However, MySQL doesn't support recursion, apparently.

Inserting duplicate records based on a value without using cursor

2 Answers2