CODEWITHSUNDEEP
snowflake-cloud-data-platform
trainingaz
trainingaz

Reputation: 11

How to loop through the records of a cursor inside another cursor in snowflake procedure?

I am trying to loop the inner cursor for each record present in outer cursor. For example:

Outer cursor contains 10 rows and inner cursor contains 2 rows. So 20 rows should get inserted into the table via this snowflake SQL proc.

I am able to insert only 2 records now by using below code.

FOR rec2 IN cur2 DO
        acct_num := rec2.ACCT_NUM;
        post_item_num := rec2.POST_ITEM_NUM;
        acc := rec2.ACCTG_DOC_ID; 
        FOR rec1 IN cur1 DO
             fis_yr := rec1.FIS_YR;
             QUERY := 'INSERT INTO INT_P(ACCTG_DOC_ID,ACCT_NUM,POST_ITEM_NUM,FIS_YR) VALUES 
                   (?,?,?,?)';
             execute immediate :QUERY using (acc, acct_num,post_item_num,fis_yr);  
        END FOR;  
    END FOR;

Simple join as a workaround cant be used in this case.

Upvotes: 1

Views: 1358

Answers (2)

Hobo
Hobo

Reputation: 7611

I think you need to open and close the inner cursor in each loop. So something like this (the open/close for cur2 may not be necessary depending on your use case):

OPEN cur2;
FOR rec2 IN cur2 DO
    acct_num := rec2.ACCT_NUM;
    post_item_num := rec2.POST_ITEM_NUM;
    acc := rec2.ACCTG_DOC_ID; 
    OPEN cur1;
    FOR rec1 IN cur1 DO
         fis_yr := rec1.FIS_YR;
         QUERY := 'INSERT INTO INT_P(ACCTG_DOC_ID,ACCT_NUM,POST_ITEM_NUM,FIS_YR) VALUES 
               (?,?,?,?)';
         execute immediate :QUERY using (acc, acct_num,post_item_num,fis_yr);  
    END FOR;  
    OPEN cur1;
END FOR;
CLOSE cur2;

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176074

It is much more perfomant to insert all records at once:

INSERT INTO INT_P(ACCTG_DOC_ID,ACCT_NUM,POST_ITEM_NUM,FIS_YR)
SELECT ACCTG_DOC_ID, ACCT_NUM,POST_ITEM_NUM, ...
FROM table_name
-- JOIN table_name2   -- outer cursor
--  ON ...

Using cursor and INSERT INTO ... SELECT :var_name, ...:

FOR rec2 IN cur2 DO
  acct_num := rec2.ACCT_NUM;
  post_item_num := rec2.POST_ITEM_NUM;
  acc := rec2.ACCTG_DOC_ID; 
  FOR rec1 IN cur1 DO
     fis_yr := rec1.FIS_YR;
     INSERT INTO INT_P(ACCTG_DOC_ID,ACCT_NUM,POST_ITEM_NUM,FIS_YR)      
     SELECT :acc, :acct_num, :post_item_num,: fis_yr;  
  END FOR;  
END FOR;

Upvotes: 1

Related Questions