Creating a Test Table with Random Data

Sometimes when you want to test a query, a new index or your new application, you'll probably need some data in it to test it properly. There are lots of different approaches to create such a test table. We won't cover all of them but here's a simple solution!

Recipe #1 - SQL Query to create a Test Table

Before we look at the final query, we'll look at two different commands we're going to use. They return random data we'll put in our table. The following query adds or removes two weeks to the current date:

SELECT SYSDATE + DBMS_RANDOM.VALUE(-14, 14) FROM DUAL;

We also want to add some random text and luckily Oracle has a function for this as well:

SELECT DBMS_RANDOM.string('A', 20) FROM DUAL;

The first parameter specifies the kind of characters we want and the second the number of characters. Check the official Oracle documentation for more about that.

Now we only have to create a query which returns rows out of air. For this, we use a hierarchical query and combine it with the two functions mentioned above. The result will then be saved in a table called test_table:

CREATE TABLE test_table AS
SELECT LEVEL id,
       SYSDATE + DBMS_RANDOM.VALUE(-1000, 1000) date_value,
       DBMS_RANDOM.string('A', 20) text_value
FROM dual
CONNECT BY LEVEL <= 100000;

Run this query and you'll have a table with 100'000 rows of random data!