Insert in Stored Procedure not committing

ckaley used Ask the Experts™
High priority
I have created a temp table that I am then dumping all the values from a view into at the start of a stored procedure.


Further down the SP fails because it finds no data and sure enough the table is empty.  This happens even if the SPs only job is to insert from the View to the Temp table and then exit.

I am at a loss.  I thought committing would do the trick.  I have tried loading the contents of the view into a for loop and inserting each row.  I have tried defining each row to get and insert into rather than *.

The insert DOES work outside of the SP so long as it is followed by a commit.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Engineer
how did you have created the table?

usually, if you want the temp table to hold data for a session, you would finish the create with a "ON COMMIT PRESERVE ROWS".


Full points for fast response and reminding me that no matter how sure you are that your copy and past got everything it probably left something behind.
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial