dynamically creating temp tables

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 i
t
in my second query.
Thanks in advance!Can't you perform the join within the same block of dynamic SQL?
"HP" <HP@.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!|||Hi HP
You already have an active thread going on this topic, in this newsgroup;
you do not need to start another one.
Thanks
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.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 selec
t
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.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can't you perform the join within the same block of dynamic SQL?
>
> "HP" <HP@.discussions.microsoft.com> wrote in message
> news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>
>|||> 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 )