Quickest way to fill SQL Table with Dummy Data [closed]

Question

What is the quickest way to fill a SQL table with dummy data?

I have a wide table with about 40 fields of different kinds (int, bit, varchar, etc.) and need to do some performance testing. I'm using SQL Server 2008.

Remediosremedy

Answer 1

You Only need Go 1000 after your INSERT, to fill it 1000 times, just like this:

INSERT INTO dbo.Cusomers(Id, FirstName, LastName) VALUES(1, 'Mohamed', 'Mousavi')
GO 1000

It will make a table with 1000 same rows in it.

Another solution is that you can populate the beginning rows of your table with some data, then you fill the next rows of table by repeating the beginning rows over and over, it means you fill your table by itself:

INSERT INTO dbo.Customers
SELECT * FROM dbo.Customers 
GO 10

In the case one or more column are identity (meaning they accept unique values, if it's auto incremental), you just don't place it in your query, for instance if Id in dbo.Customer is identity, the query goes like this:

INSERT INTO dbo.Customers
SELECT FirstName, Last Name FROM dbo.Customers
GO 10

Instead Of:

INSERT INTO dbo.Customers
SELECT Id, FirstName, Last Name FROM dbo.Customers
GO 10

Else you'll encounter this Error:

An explicit value for the identity column in table 'dbo.Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Note: This is sort of an arithmetic progression, so it's going to last a little, don't use a big number in front of GO.

If you want to have a table which is filled a little bit more elaborated then you can achieve that the same way this time by executing a simple query and following these steps:

1. Choose one of your tables which has a remarkable number of rows, say dbo.Customers

2. Right click on it and select Script Table as > Create To > New Query Editor Window

3. Name your new table to something else like dbo.CustomersTest, Now you can execute the query to have a new table with similar structure with the dbo.Customers.

Note:Keep in mind that if it has a Identity filed, change it's Identity Specification to No Since you are supposed to fill the new table by the data of the original one repeatedly.

4. Run the following query, it's going to be run 1000 times, you can change it to more or less but be aware that it might last minuets based on your computer hardware:

INSERT INTO [dbo].[CustomersTest] SELECT * FROM [dbo].[Customers] GO 1000

5. After a while you have a table with dummy rows in it!

---

As @SQLMenace mentioned, RedGate Data Generator is a so good tool to fulfill it, it costs $369, you have a 14 days trial chance Although.

The good point is that RedGate identifies foreign keys so you can apply JOIN in your queries.

You have a bunch of options which allow you to decide how every column is supposed to be populated, every column is anticipated semantically so that related data are suggested, for instance if you have a column named 'Department' it isn't filled by weird characters, it's filled by expressions like "Technical", "Web", "Customer", etc. Even you can use regular expression to restrict selected characters.

I populated my tables with over 10,000,000 records which was an awesome simulation.

Letha

Answer 2

Late answer but can be useful to other readers of this thread. Beside other solutions, I can recommend importing data from a .csv file using SSMS or custom SQL import scripts, programs. There is a step-by-step tutorial on how to do this, so you might want to check it out: http://solutioncenter.apexsql.com/how-to-generate-randomized-test-data-from-a-csv-file/

Be aware that importing a .csv file using SSMS or custom SQL import scripts, is easier than creating SQL inserts manually, but there are some limitations, as explained in the tutorial:

If there is a need for thousands of rows to be populated, and the .csv file contains few hundred rows of data it is just not enough. The workaround is reimporting the same .csv file over and over until needed. The drawback to this method is that it will insert large blocks of rows with the same data, without randomizing them.

The tutorial also explains how to use a 3rd party SQL data generator called ApexSQL Generate. The tool has an integrated function to generate large amounts of randomized data from the imported .csv formatted file. Application features a fully functional free trial so you can download and try it to see if it works for you.

Cushat

Answer 3

http://filldb.info/dummy/ works best. It offers complete settings, choice of how many rows to generate, "real" dummy data, all for free.

I've never seen anything more effective or better at this conditions.

You can generate a whole database or just a table with an easy to use GUI. It is also very elaborate in its settings and options, allowing you to generate dummy data with basically no effort. The GUI has no limits in size and is very extensive in data type options.

To use it, navigate to the link and insert a SQL command that defines the tables or use their dummy tables. Then click next and fill out your rows data types and settings for dummy data population. Then click next and generate the data. Wait. Once done, download the database and import it to your own database server.

Krenek

Please don't just post some tool or library as an answer. At least demonstrate how it solves the problem in the answer itself. – Granvillegranvillebarker

Ok, just the accepted answer neither the second most voted answer do that either. Just saying… I even explain why it’s a good solution: complete settings, choice of how many rows to create, real dummy data. Do I need you to show how to use the tool? Then why do the other auch answers not include that? – Krenek

Thanks for editing the answer. Note that just explaining why it's a good solution is not really sufficient, you need to also demonstrate how to use the tool (as you have done in the last paragraph). Also, you're right that the top 2 scoring answers are link-only. I've flagged them as VLQ (which is also something you can do for such answers). – Granvillegranvillebarker

Ok… will keep in mind for future postings – Krenek

Looks great, but I tried it and it gives a progress bar and then a success message saying it generated data, but stays at 0 rows. – Yuletide

Note that this tool is for MySQL/MariaDB only. – Karilla

I've successfully generated 10000 rows for my 20-column table using this tool. I have MSSQL and the generated script worked fine. Huge thanks for user3934058. I saved this tool for the future – Pontus