Prev: a smart CASE statement
Next: Select returns no rows within TRY... END TRY
From: Rick on 18 Jul 2008 11:26
I'm doing that I thought would work but maybe not: trying to use dynamic
SQL to create a temp table via SELECT / INTO. Here's a stripped-down
version of my code:

DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT * '
+ 'INTO #mytemp '
+ 'FROM any_other_table o '
+ 'WHERE o.subm_no = 118731'

EXEC sp_executesql @sql

But it doesn't create #mytemp at all.

Any thoughts?
From: Plamen Ratchev on 18 Jul 2008 11:49
The problem is that a local temporary table created at one level is not
visible (out of context) to outer levels. The table gets created in the
dynamic sql batch but you cannot query the table after the batch is
executed.

You can create the temporary table in advance (before invoking the dynamic
sql) and then insert in your dynamic sql. If the table structure is unknown
in advance then you can still create it at the outer level with a dummy
column, and then inside a dynamic sql batch alter and add the columns you
need/drop the dummy column, followed by insert in another dynamic batch.

Perhaps revise and reconsider using dynamic sql. A good reading by Erland
Sommarskog:
http://www.sommarskog.se/dynamic_sql.html

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Rick on 18 Jul 2008 12:38
Thank you; that does it.

In article <46AF48CE-BE80-4FEF-A067-850969643C6B(a)microsoft.com>,
Plamen(a)SQLStudio.com says...
> The problem is that a local temporary table created at one level is not
> visible (out of context) to outer levels. The table gets created in the
> dynamic sql batch but you cannot query the table after the batch is
> executed.
>
> You can create the temporary table in advance (before invoking the dynamic
> sql) and then insert in your dynamic sql. If the table structure is unknown
> in advance then you can still create it at the outer level with a dummy
> column, and then inside a dynamic sql batch alter and add the columns you
> need/drop the dummy column, followed by insert in another dynamic batch.
>
> Perhaps revise and reconsider using dynamic sql. A good reading by Erland
> Sommarskog:
> http://www.sommarskog.se/dynamic_sql.html
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com
>
>
 | 
Pages: 1
Prev: a smart CASE statement
Next: Select returns no rows within TRY... END TRY