|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Returning a table into a SP from an SPI have a SP called SP_GetTable. In a seperate SP how do perform the
following? CREATE procedure SP_GetValuefromSP as DECLARE @MyTable (@Field1 Integer, @Field2 Integer) Insert into @MyTable [Results from SP_GetTable] return 0 go Thanks, Dave. I think I've figured it out. You have to create a temporary table as
you cannot insert the results of a SP into a memory table. Are there any "dangers" to using Temporary Tables? These SP will not be run often (maybe once every week) Thanks again, Dave. On Thu, 12 May 2005 09:00:33 -0400, Dave <D***@here.ca> wrote: Show quote >I have a SP called SP_GetTable. In a seperate SP how do perform the >following? > >CREATE procedure SP_GetValuefromSP >as > >DECLARE @MyTable (@Field1 Integer, @Field2 Integer) > >Insert into @MyTable [Results from SP_GetTable] > >return 0 >go > > > >Thanks, > >Dave. On Thu, 12 May 2005 09:08:06 -0400, Dave wrote:
>I think I've figured it out. You have to create a temporary table as Hi Dave,>you cannot insert the results of a SP into a memory table. Are there >any "dangers" to using Temporary Tables? These SP will not be run >often (maybe once every week) A table variable is stored in tempdb. If it's small and short-lived, it will never make it from cache to the disk - but the same can be said about a temp table! Because temp table operations are not logged, there is some reduction in disk activity for the log file. But calling a table variable "memory based" is misleading. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hugo,
Sorry for nit-picking, just one small detail: > Because temp table operations are not logged, Operations against temp tables are logged. Same goes for table variables, with a little bit less logging compared to temp tables. Only UNDO operations are logged for both, not REDO operations. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:s2j7815r2crptq8clfac0d36sj2esf4fd6@4ax.com... > On Thu, 12 May 2005 09:08:06 -0400, Dave wrote: > >>I think I've figured it out. You have to create a temporary table as >>you cannot insert the results of a SP into a memory table. Are there >>any "dangers" to using Temporary Tables? These SP will not be run >>often (maybe once every week) > > Hi Dave, > > A table variable is stored in tempdb. If it's small and short-lived, it > will never make it from cache to the disk - but the same can be said > about a temp table! Because temp table operations are not logged, there > is some reduction in disk activity for the log file. But calling a table > variable "memory based" is misleading. > > "Should I use a #temp table or a @table variable?" www.aspfaq.com/2475 > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) On Fri, 13 May 2005 08:33:11 +0200, Tibor Karaszi wrote:
>Hugo, Hi Tibor,> >Sorry for nit-picking, just one small detail: > >> Because temp table operations are not logged, > >Operations against temp tables are logged. Same goes for table variables, with a little bit less >logging compared to temp tables. Only UNDO operations are logged for both, not REDO operations. Don't apologize for catching my goofs. I don't know how I managed to write and post that. I meant to write "Because table variable operations are not logged" - and obviously, even that would not heve been correct (but at least less incorrect than the nonsense I did post <blush> Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hi Hugo,
> I meant to write "Because table variable operations And that is how I first read it, and started typing based on that; before re-reading your post. :-)> are not logged" -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:jjea8150j1gaifesscmvrhpuehgcqmfdt0@4ax.com... > On Fri, 13 May 2005 08:33:11 +0200, Tibor Karaszi wrote: > >>Hugo, >> >>Sorry for nit-picking, just one small detail: >> >>> Because temp table operations are not logged, >> >>Operations against temp tables are logged. Same goes for table variables, with a little bit less >>logging compared to temp tables. Only UNDO operations are logged for both, not REDO operations. > > Hi Tibor, > > Don't apologize for catching my goofs. I don't know how I managed to > write and post that. I meant to write "Because table variable operations > are not logged" - and obviously, even that would not heve been correct > (but at least less incorrect than the nonsense I did post <blush> > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics