Problem

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 solutions!

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. You'll hopefully know what sysdates means and if you also know that you can add a day by adding +1 to it, you know almost everything. The following query uses this, to add or remove 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 specified the kind of characters we want and the second the number of characters. Check the official Oracle documentation for more about that http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_random.htm#i996825

Now we only have to create a query which returns rows out of air. For this, we use an 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!