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