Sql Populate table with random data

sql

random-data

sql-functions

postgresql

byNikita Barsukov·Jan 4, 2025

For a lightspeed, down-and-dirty way to zap random data into your table, salad-dress it with RAND() and NEWID(). This SQL invocation tosses random integers and unique identifiers into your columns like you wouldn't believe:

INSERT INTO YourTable (IntColumn, UUIDColumn)
SELECT
  ROUND((RAND() * (MaxValue - MinValue)) + MinValue) AS RandomInteger, -- Ring-a-ding! Here's a random integer
  NEWID() AS UniqueIdentifier -- Whoosh! A wild UUID appeared
FROM
  YourLargeTable;

Just sashay your finger over to replace YourTable, IntColumn, UUIDColumn, MinValue, MaxValue, and YourLargeTable with the real deal for your database. Baby got back data with variety and unpredictability!

Upping your randomness game in SQL Server

Feel like turning up the ante on the randomness roulette? The CHECKSUM and NEWID() functions are ready to rumble! Fuel their fire with the input data for each row and watch the magic happen:

INSERT INTO YourTable (StringColumn, DateColumn)
SELECT
  SUBSTRING(CONVERT(varchar(36), NEWID()), 1, 8) AS RandomString, -- Whoop! Now ain't that a pretty random string!
  DATEADD(day, (ABS(CHECKSUM(NEWID())) % 365), GETDATE()) -- Abracadabra! Pack your bags for time travel to a random date!
FROM
  YourLargeTable;

This XYZZY-esque incantation guarantees that your data populates like rabbits while staying cage-free organic.

PostgreSQL's take on random data—talk about wild rides

Feel like rocking the PostgreSQL vibe? Its md5 and random functions can throw a rave party of randomness:

INSERT INTO YourTable (ID, Description)
SELECT
  s, -- Here comes our contender, a sequential integer!
  md5(random()::text) -- And the crowd goes wild with a unique hash!
FROM
  generate_series(1, 1000000) s;

This bad boy pumps up your PostgreSQL table to a cool million rows, all flexing their sequential ID muscles and showing off random descriptions for flair.

Put some pep in your step with UUID

Don't let the primary key column feel left out of the party. Give it a UUID data type, and it'll never show up empty-handed:

-- PostgreSQL example for a UUID primary key
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
INSERT INTO YourTable (UUIDColumn, OtherData)
SELECT
  uuid_generate_v4(), -- Gotcha! A freshly minted UUID, hot from the press
  md5(random()::text) -- A figment from the realm of randomness
FROM
  generate_series(1, 10000);

You’ve now got a flock of unique primary key identifiers for your PostgreSQL table, each chirping its tune.

Scoring with strings—getting naughty with SQL

Make 'em strings sing

Strings need not keep to single notes. Roll your drum and summon string functions like LEFT, RIGHT, and CONCAT to create a harmony:

INSERT INTO YourTable (Username, Password)
SELECT
  CONCAT('user', s.id), -- There goes our username, strutting like a peacock. Rumble!
  LEFT(md5(random()::text), 10) -- It’s raining passwords—check out that sleek left slice!
FROM
  generate_series(1, 10000) AS s(id);

Voila! You've made your placeholders mimic real-world data and won that round.

Crafting modern art data

Random data doesn't have to look like a cat walked on the keyboard. Get Picasso-y and create composite columns:

INSERT INTO YourTable (ComplexDescription)
SELECT
  string_agg(md5(random()::text), ' ') -- Lights, camera, action! Here comes a blockbuster string_agg!
FROM
  generate_series(1, 10);

You've just produced a medley of words that could form poetic addresses or whimsical product names. That's one for the gallery!

Read like a poet, code like a boss

No need to go Hemingway on random words yourself. Let the Unix dictionary do the honors and bless your PostgreSQL with literary gems:

CREATE OR REPLACE FUNCTION RandomWord()
RETURNS text AS $$
DECLARE
  lines text[];
BEGIN
  lines := string_to_array(pg_read_file('/usr/share/dict/words', 0, 4000000), E'\n');
  RETURN lines[(random() * array_length(lines, 1))::int];
END;
$$ LANGUAGE plpgsql VOLATILE;

INSERT INTO YourTable (WordColumn)
SELECT RandomWord() -- Here's to you, Mr. Random-word-picker. Swing it!
FROM generate_series(1,10000);