Mysql - Create sample data from existing rows

Question

I have a table with about 50K rows. I need to multiply this data 10 fold to have at least 5M rows for testing the performance. Now, its taken me several long minutes to import 50K from a CSV file so I don't want to create a 5M record file and then import it into SQL.

Is there a way to duplicate the existing rows over and over again to create 5M records? I don't mind if the rows are identical, they should just have a diferrent id which is the Primary (Auto Increment) column.

I'm currently doing this on XAMPP with phpMyAdmin.

what your application platform? Are you using any MVC like laravel? — Md. Abutaleb
– Md. Abutaleb, Commented May 25, 2017 at 5:00
We haven't started working on the website yet. Infact, we are planning to test the performance of various db systems and php frameworks with this data. — Whip
– Whip, Commented May 25, 2017 at 5:02

Strawberry · Accepted Answer · 2017-05-25 06:44:08Z

2

Insert into my_table (y,z) select y, z from my_table;

where x is your autoincrementing id.

REPEAT a (remarkably small) number of times

answered May 25, 2017 at 6:44

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hydrocat Over a year ago

about 6 repetition would get you close to 100x the amount of data.

Strawberry Over a year ago

Yep, as true today as it was 2.5 years ago

Akhil · Accepted Answer · 2017-05-25 05:13:18Z

0

Option 1 : Use union

 insert into your_table (col1,col2)
    select col1,col2  from your_table
    union all 
    select col1,col2 from your_table
    union all 
    select col1,col2 from your_table
    union all 
    select col1,col2 from your_table
    continued...

Option 2 : Use dummy table with 10 records and do cross join

Create a dummy table with 10 rows

insert into your_table (col1,col2)
select col1,col2 from your_table, dummy_table

edited May 25, 2017 at 5:13

answered May 25, 2017 at 5:05

Akhil

2,60225 silver badges37 bronze badges

3 Comments

Barmar Over a year ago

SELECT * won't work with the primary keys, you'll get duplicate key errors.

Barmar Over a year ago

You need to select all the columns except the id column.

Strawberry Over a year ago

You're overthinking this.

sid-m · Accepted Answer · 2017-05-25 05:34:27Z

0

If you have ~50K rows, then copying them 99 times will give you ~5M rows. To do so, you can create a procedure and use a loop to copy them 99 times.

DELIMITER $$
CREATE PROCEDURE populate()
BEGIN
  DECLARE counter INT DEFAULT 1;

  WHILE counter < 100 DO
    insert into mytable(colA, colB) select colA, colB from mytable;
    SET counter = counter + 1;
  END WHILE;
END $$

DELIMITER ;

Then you can call the procedure using

call populate();

answered May 25, 2017 at 5:34

sid-m

1,55412 silver badges19 bronze badges

1 Comment

Hydrocat Over a year ago

Hey, I have to say that it isn't 99 times but around 6 ! You see, since you are coping the same table over and over, the amount of lines is exponentially growing.

Collectives™ on Stack Overflow

Mysql - Create sample data from existing rows

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related