Transfer from http://blog.csdn.net/welken/article/details/4971887 do database development or management people often want to create a large number of test data, there is no need to tens of thousands of, if one entry, it will waste a lot of time, This article describes how to quickly generate a large amount of test data in Oracle using a single SQL method. The SQL that produces the test data is the following:sql> select RowNum as ID,
2 To_char (sysdate + rownum/24/3600, ' yyyy-mm-dd hh24:mi:ss ') as Inc_datetime,
3 trunc (dbms_random.value (0)) as random_id,
4 dbms_random.string (' x ', random_string)
5 from dual
6 Connect by Level <= 10;
ID inc_datetime random_id random_string
---------- ------------------- ---------- ------------------------------------------------------------------------ --------
1 2009-12-08 19:43:14 gwmu280mivbkkoczv620
2 2009-12-08 19:43:15 Gnv88o6tdhd3twc5gwi5
3 2009-12-08 19:43:16 LI6H4O5IAHQIMO4B0WMH
4 2009-12-08 19:43:17 Lp7xp49i0yojiysjdqzo
5 2009-12-08 19:43:18 V3284x9rxw4uzi8bqmo3
6 2009-12-08 19:43:19 t0oa52uaoghl1tt46h25
7 2009-12-08 19:43:20 UY6RUOF7HWTO86942FLP
8 2009-12-08 19:43:21 JYXO4OPEW8J1CKVCPDJR
9 2009-12-08 19:43:22 Donu6w9qvqm3kj2ug8lo
Ten 2009-12-08 19:43:23 J8DJLVNOUIZDXE4UXUJG
Rows selected
The above SQL is implemented using several practical tips for Oracle database syntax:
1, using Oracle's unique "Connect by" tree connection syntax to generate test records, "level <= 10" means to generate 10 records;
2, using rownum virtual column to generate incremental integer data;
3, using the Sysdate function plus some simple operations to generate date data, in this case, the time of each record plus 1 seconds;
4, using the Dbms_random.value function to generate random numerical data, in this case, a random integer from 0 to 100 is generated;
5, the use of the dbms_random.string function to generate random character data, in this case, the generation of a random string length of 20, the string can include characters or numbers. OK, then to generate 100,000 test record tables can be used sql:create table mytesttable as
Select RowNum as ID,
To_char (sysdate + rownum/24/3600, ' yyyy-mm-dd hh24:mi:ss ') as Inc_datetime,
Trunc (dbms_random.value (0)) as random_id,
Dbms_random.string (' x ', ') random_string
From dual
Connect by level <= 100000;
"Go" how to quickly generate 100,000 test data with one SQL in Oracle
