These are needed in order to create the temp table definition. When creating a procedure, the goal should be to make it as atomic as possible. NsatSurveyEnabled flag turn on in. Question: How to Insert Results of Stored Procedure into a Temporary Table? So with that in mind, we need to first create the temp table prior to executing the procedure. The procedures and output are complex and number of columns is high, it would take me a bit of time to work out the table structure each proc outputs. That is, return the result to the client. If there is an easy way to do this it would be great.
Looping in external application and each time sending a separate query to the database for each record. If there was a competition to find the worst way it was winner :- First. Creating the table is just the start of it. How would query this table when don't know what columns are? This is not very clear nor obvious. To do anything else would cause either performance problems or security issues dynamic sql. If there was a competition to find the worst way it was winner :- First.
Needless to say that, the help regarding my performance issue that I was looking for is finally I have. Development manager probably scream and hold his head! Edit: I just saw your post and I advise anyone who use select into to be very very careful. If forces you to code for all data types you'd like to support, all integrity rules you want to enforce, etc. Notes and comments in code can be wonderful things when used properly. When there is a large amount of comma separated values as the input parameter, the code can be delayed for a few seconds while inserting the data into the temp table. Meaning it should only serve one purpose.
After that will come the execution and then as many optimizations as possible. I do correct it above already! While the feature is good, what is untold by most is how this method signficantly drags performance. Gert-Jan I think the answer above is a simple minded answer. Thanks Assuming I correctly understand this, the error msg is saying I can't use a Stored procedure to call another Stored Procedure. Forgive my newbie-ness but I'm still pretty new when it comes to stored procedures.
An important observation is that when you run into situations like this where the tool does not seem to have the feature you want, you have probably deviated from the norm on an earlier stage. The best is to define your table in advance. I'm fairly new to this myself. Após isso dá merda se chamo o record set. One thing I don't like about this approach is it can duplicate account records. Development manager probably scream and hold his head! And that doesn't seem possible with a proc. These tables are visible and accessible within the session only.
I thought it was going to be a quick answer telling me it wasn't really possible. Hi, I wanted to insert the result-set of a Exec sqlcommand into a temp table. Since I don't, would there be anything similar to table types as parameters? Data is inserted quickly in the temporary table, but if the amount of data is large then we can experience poor query performance. I am troubleshooting the output from two procedures to find discrepancies. Another time I had problem with this because I am using this task in another StoredProcedure, and it was working fine on my machine.
Make sure to replace the server and instance with your servernameinstancename values. If you keep doing the same thing, you'll always get the same thing. There can be numerious solution for a problem. I found that inside the stored procedure was already code similar to that which you each proposed. I have already spent quite some time on the parser; it is a lot of work. Consider also logging and how you can affect it.
Also, may be I was not clear. And you should drop it of course once you are done with it. You take the data out to external application from the database just to put it back in?!? You can get away with it if you escape the relational world at the same time as you produce the pivoted result. How would query this table when don't know what columns are? There is also the alternative to use a global temp table. Currently, I am working on the parsing part. Often times true developers are implementing pivot tables because it presents the data as the user will like to see it without the intervention of the user.
I found that inside the stored procedure was already code similar to that which you each proposed. Any other feedback is greatly appreciated! I'm just trying to avoid having to create the table unless I have to and learn some new tricks at the same time. This usually happens with temporary tables when we insert a large number of rows. How many rows are typically loaded into the Temp Table in the stored procedure? Ok, so here's something weird to throw into the mix. When you come back to it, you won't have to try to remember where you were at with the problem. I can always create a table and insert into it from the stored procedure but how nice would it be if I could just do the whole thing in one process only. But am preparing a dynamic-sql command and storing that in variable sqlcommand and the output changes for each query execution.