Couple of approaches to generate test-data in bulk

| By Webner

Sometimes you need to fill a database table with thousands of test data records. You may also need to generate test data in the form of csv file to feed to a program. How can you generate such a large set of data in short time?

One approach to generate this type of data is to use excel, Google Sheets or any spreadsheet software and use formulas. Like you can list numbers from 1 to 10000 in one column and then using this column you can generate data in other cells to get 10000 unique records. For example if you need to generate 10000 unique usernames then you can write =concat(“username”,A1) where A1 is the first cell of that column that has numbers from 1 to 10000. You can then copy this formula 10000 times to get username1, username2 etc. You can also apply some advanced logic instead of simple concatenation (like you can check if number is odd then do this else do that).

If your data is already present in database but dispersed in different tables then you can also use the power of SQL. For example suppose you have 2 different databases. In first database you have table1, table2 and table3. In database2 you have table2. You need data of specific columns from table1, table2 and table3 which will get inserted into table5 in database2. You can generate SQL INSERT queries using similar approach of spreadsheet formulas as given below in sample query:

Here is an example for MySQL:

SELECT  CONCAT('INSERT INTO table5 (column1,column2) VALUES (' ,id,',',QUOTE(firstName), ');')  as test_data FROM table1, table2, table3 WHERE ……..;

QUOTE is the mysql built in function to put strings in quotes.

This will generate several queries like this:

INSERT INTO table5(id,name) values (1, 'user1);
INSERT INTO table5(id,name) values (2, 'user2');
INSERT INTO table5(id,name) values (3, 'user3');

You can copy these queries and paste in SQL console of 2nd database and execute them

Webner Solutions is a Software Development company focused on developing CRM apps (Salesforce, Zoho), LMS Apps (Moodle/Totara), Websites and Mobile apps. If you need Web development or any other software development assistance please contact us at

Leave a Reply

Your email address will not be published. Required fields are marked *