巧用CTE来实现生成10000行数据

这个是这几天工作的基础之一,我首先需要生成10000数据.原来知道在Oracle中经常是访问一个系统表,然后反复Insert来实现的.但我们的环境中数据量实在太少,所以只好用这个方法了.

INSERT INTO FFINTTB (INTCARD1,INTCARD2,INTCARD3,INTCARD10,INTCARD100,
INTCARD1000,   INTCARD10000,                                        
INTCARD1NN,INTCARD2NN,INTCARD3NN,INTCARD10NN,INTCARD100NN,          
INTCARD1000NN, INTCARD10000NN)                                      
WITH SAMP (C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14          
)                                                                   
AS (                                                               
SELECT  1,1,1,1,1,1,1,1,1,1,1,1,1,1 FROM SYSIBM.SYSDUMMY1           
UNION ALL                                                           
SELECT C1+1,C2+1,C3+1,C4+1,C5+1,C6+1,C7+1,C8+1,C9+1,C10+1,          
C11+1,C12+1,C13+1,C14+1                                             
FROM SAMP WHERE C1 < 100000                                        
)                                                                   
SELECT C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14               
FROM SAMP;

我的实现中使用了14个column,在实际中只要根据需要就行了.

转载于:https://www.cnblogs.com/db2zos/archive/2011/01/07/1929943.html