All geek questions in one place

Running a large oracle SQL script in a loop

I have created most of the script that inserts new rows at the very end in my target table.

Script contains many selection statements and staging tables.

I want the loop to be run 2,000 times in a row and see no alternative other than pressing f5 2,000 times.

Is there an Oracle SQL equivalent for porting code to a SAS macro and its loop 2000 times?

+5
source share
2 answers

In this situation, it is best to use an anonymous PL / SQL block:

BEGIN FOR i IN 1..2000 LOOP -- Insert scripts go here END LOOP END; /
+2
source

Can you try converting the inserts to pure SQL ?. This is the fastest and cleanest approach to downloading large amounts of data.

After you are set up, row_source generation can be done by Cartesian processing of the result.

For example: first let's say that your table has

 EmpNo, EmpName, Sal 1000 , Mark , 500 1001 , Jorja , 100

I want to generate the contents of a table 3 times, then I will do the following

 insert into emp select (select max(empno) from emp )+lvl as empNo , empName , Sal from emp join (select level as lvl from dual connect by level<=3 )row_source_generation on 1=1;

This will give the following conclusion

 EmpNo, EmpName, Sal 1000 , Mark , 500 1001 , Jorja , 100 1002 , Mark1 , 500 1003 , Jorja1 , 100 1004 , Mark2 , 500 1005 , Jorja2 , 100 1006 , Mark3 , 500 1007 , Jorja3 , 100
0
source

Source: https://habr.com/ru/post/1259618/

More articles:


All Articles