How can I populate my database's all tables with random data?

Question

Is there such a software out there? It doesn't matter if the data itself make sense. I am just worried about the fields to get populated. Basically, it will read the table definitions and generate some data accordingly. It would also be great if it asks for how many rows to insert per table, whether the default values will be left blank or get populated, how to treat the varchars(to the full extent or up to a given,specified number of characters). Ideally free :) but commercial product suggestions are also highly welcome. Thank you all.

Pleurodynia

do you want query for this? – Kept

Not really. I just want to click a magic button and all the tables get filled in with data.. – Pleurodynia

you want randomly filled data? – Kept

Exactly. I don't mind about the quality or the meaning of the data. – Pleurodynia

SELECT * FROM quotes ORDER BY RANDOM() LIMIT 1 use this query on magic button action – Kept

The databases I work with consist of tables with at least 30 fields.Sometimes, I need to enter random data for testing but it gets cumbersome to run insert statement for each one of them, then they fail with not null exceptions etc.. – Pleurodynia

Sorry but couldn't quite get it. What does this query suppose to do? SELECT * FROM quotes ORDER BY RANDOM() LIMIT 1 I don't have any data in place in any of the tables. – Pleurodynia

this query will select random data for your table when you click your magic button – Kept

here quotes is your table name – Kept

Ok. There is a big misunderstanding here. There is no such thing as "magic button" at my convenience. I wrote it to refer to the fact that I am looking for a software with a "magic button" which will get my database populated once clicked. Just forget about this term. I am after a software to fill my database's tables' fields. That's all. I'm on SQL Server so don't know Random function as well as limit operator. It should be some platform-dependent commands. Thank you. – Pleurodynia

possible duplicate of Looking for a SQL test set generator, ideally open source – Petrapetracca

Answer 1

I see there is still no answer that has been chosen as the best. Besides the solutions already mentioned in this thread, I can suggest using ApexSQL Generate – a SQL data generator tool, which has the ability to quickly generate millions of rows, using various data sources, with a variety of data generators.

It features a fully functional free trial, so you can download it and test to see if it will get your job done.

Perionychium

Answer 2

For mysql databases you can use filldb.info.

It automatically generate data and fill your database tables with this dummy data

Chamade

Answer 3

In case someone is looking for a simple way to do this without extra software.

We just need a function that returns a random number:

-- Create the RANDBETWEEN function
-- Usage: SELECT dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID())))
CREATE FUNCTION dbo.RANDBETWEEN(@minval TINYINT, @maxval TINYINT, @random NUMERIC(18,10))
RETURNS TINYINT
AS
BEGIN
  RETURN (SELECT CAST(((@maxval + 1) - @minval) * @random + @minval AS TINYINT))
END
GO

And then you can run a query like this:

-- Insert 1 million records into the Person table
INSERT INTO Person (FirstName,LastName,CityId)
SELECT TOP 1000000
    CASE
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 0 THEN 'John'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 1 THEN 'Jack'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 2 THEN 'Bill'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 3 THEN 'Mary'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 4 THEN 'Kate'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 5 THEN 'Matt'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 6 THEN 'Rachel'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 7 THEN 'Tom'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 8 THEN 'Ann'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 9 THEN 'Andrew'
    ELSE 'Bob' END AS FirstName,
    CASE
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 0 THEN 'Smith'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 1 THEN 'Morgan'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 2 THEN 'Simpson'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 3 THEN 'Walker'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 4 THEN 'Bauer'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 5 THEN 'Taylor'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 6 THEN 'Morris'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 7 THEN 'Elliot'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 8 THEN 'White'
        WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 9 THEN 'Davis'
    ELSE 'Brown' END AS LastName,
    dbo.RANDBETWEEN(1,15,RAND(CHECKSUM(NEWID()))) as CityId
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

Result:

Adjust the query accordingly to match your table/column names.

Identification

Answer 4

the very simple SQL I use generally :

create table fillTbl (msg nvarchar(255))
insert into fillTbl select top 4285 description from sys.sysmessages where msglangid=1033 -- ~ 1 Mo
GO 100 -- the number of Mo you want to add
exec sp_spaceused 'fillTbl' -- space in the table
exec sp_spaceused -- space in the database
-- drop table fillTbl -- reset the space added

Adriannaadrianne

Answer 5

You could use the SQLSynthGen Python library, as long as your database is supported by SQLAlchemy. It will generate completely random data by default, without any configuration. It can handle foreign key constraints, etc. though may require some customisation if you have, for example, check constraints.

Sort