Insert results from stored procedure into another table

Comrade · #1 (**permalink**) 11-11-11, 16:57

Hello,

I have a stored procedure which returns some data upon execution. How do I insert the data into another table (not temporary)? I have appropriately created the table in which the data is to be inserted.

There are a few threads on the topic, but some of them are related to temporary table and I don't follow the discussion very well.

ARWinner · #2 (**permalink**) 11-14-11, 09:35

You will have to do it with another Stored Procedure or at the application level. There is no direct way to do it with a single SQL statement.

Andy

Comrade · #3 (**permalink**) 11-14-11, 14:51

Thanks, Andy. I think I'll write a stored procedure then. Can you point me to some direction? Can I just use select and insert statements to achieve what I need?

ARWinner · #4 (**permalink**) 11-14-11, 14:57

You will have to loop through the result set of the stored procedure like this:

Code:

         call MySchema.MySP(z_Parm1,z_Parm2,z_Parm2);
         ASSOCIATE RESULT SET LOCATOR (local_RS_Locator) with procedure MySchema.MySP;
         ALLOCATE Local_Cursor CURSOR for result set local_RS_Locator;
         SET ENDTABLE = 0;
         open Local_Cursor;
         fetch Local_cursor into col1,col2,col3;
         WHILE (endtable = 0) do
            insert into MySchema,MyTable values (col1,Col1,col3);

            fetch Local_cursor into col1,col2,col3;
         end while;

Andy

Internet Services

Web Development

Digital Marketing

Consumer Tech