Insert in Stored Procedure not committing

ckaley
ckaley used Ask the Experts™
on
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.

INSERT INTO TG_ACTIVE_ACTIVITIES_TEMP_TB (SELECT  * FROM TG_ACTIVE_ACTIVITIES_VW);
commit;

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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Engineer
Commented:
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".

Author

Commented:
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