Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: Insert results from stored procedure into another table

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Nov 2011
    Posts
    5
    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?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •