|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
dynamically creating temp tablesI have a dynamic sql which uses "select into" to create a temp table. It has
to be dynamic because the fieldnames are soft coded.I need to join this temp table (that i created using the dynamic sql) with another table.Since the temp table goes out of scope after the exec statement i am not able to use it in my second query. Thanks in advance! Can't you perform the join within the same block of dynamic SQL?
Show quote "HP" <H*@discussions.microsoft.com> wrote in message news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@microsoft.com... >I have a dynamic sql which uses "select into" to create a temp table. It >has > to be dynamic because the fieldnames are soft coded.I need to join this > temp > table (that i created using the dynamic sql) with another table.Since the > temp table goes out of scope after the exec statement i am not able to use > it > in my second query. > Thanks in advance! Actually i have to use that temp table in 2 queries.If i include those select
stetements within the same block , the dynamic sql would be big, and the execution of the dynamic statement could be slow.Correct me if I am wrong. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > Can't you perform the join within the same block of dynamic SQL? > > > > "HP" <H*@discussions.microsoft.com> wrote in message > news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@microsoft.com... > >I have a dynamic sql which uses "select into" to create a temp table. It > >has > > to be dynamic because the fieldnames are soft coded.I need to join this > > temp > > table (that i created using the dynamic sql) with another table.Since the > > temp table goes out of scope after the exec statement i am not able to use > > it > > in my second query. > > Thanks in advance! > > > > Actually i have to use that temp table in 2 queries.If i include those You're using dynamic SQL and #temp tables. I doubt the size of your dynamic > select > stetements within the same block , the dynamic sql would be big, and the > execution of the dynamic statement could be slow.Correct me if I am wrong. SQL is going to have a measurable impact on that kind of performance. If the dynamic SQL is too big for a single varchar(8000) you can always try: EXEC ( @tempTableCreation +';' + @sqlJoin1 + ';' + @sqlJoin2 ) My dynamic sql is not too big.If i throw in the other join as you suggested
i'll have 3 select queries.I am basically worried about performance. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > Actually i have to use that temp table in 2 queries.If i include those > > select > > stetements within the same block , the dynamic sql would be big, and the > > execution of the dynamic statement could be slow.Correct me if I am wrong. > > You're using dynamic SQL and #temp tables. I doubt the size of your dynamic > SQL is going to have a measurable impact on that kind of performance. > > If the dynamic SQL is too big for a single varchar(8000) you can always try: > > EXEC ( @tempTableCreation +';' + @sqlJoin1 + ';' + @sqlJoin2 ) > > > > My dynamic sql is not too big.If i throw in the other join as you And I am suggesting that the fact that you already have dynamic SQL and temp > suggested > i'll have 3 select queries.I am basically worried about performance. tables, and you have to do the three joins regardless, the difference between doing the three joins inside of dynamic SQL or not is really going to have negligible impact on performance, if any. Of course, the onus is on you to test and make sure the performance is still acceptable. In the time you have worried about it and debated with me, you could have already tested it. :-( If performance is your main worry, then don't use dynamic SQL. Consider the
pros and the cons - if the benefits of using one dynamic procedure outweigh the performance benefits of using several procedures, then the answer is quite obvious, isn't it? ML HP (H*@discussions.microsoft.com) writes:
> My dynamic sql is not too big.If i throw in the other join as you I you are creating tables with column names generated at run time,> suggested i'll have 3 select queries.I am basically worried about > performance. performance is obviously not your major concern. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp Hi HP
You already have an active thread going on this topic, in this newsgroup; you do not need to start another one. Thanks Show quote "HP" <H*@discussions.microsoft.com> wrote in message news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@microsoft.com... >I have a dynamic sql which uses "select into" to create a temp table. It >has > to be dynamic because the fieldnames are soft coded.I need to join this > temp > table (that i created using the dynamic sql) with another table.Since the > temp table goes out of scope after the exec statement i am not able to use > it > in my second query. > Thanks in advance! >  |
|||||||||||||||||||||||
Other interesting topics