Yes, the best is to create temp table in calling stored procedure and populate it in the dynamic sql. However, I was refering to the posibility to create a global temporary table as in following example.
IF object_id('tempdb..##Temptable') IS NOT NULL
DROP TABLE ##Temptable
DECLARE @sql varchar(4000)
SET @sql = 'SELECT * INTO ##Temptable FROM pubs.dbo.Authors'
EXECUTE(@sql)
SELECT * FROM ##Temptable
IF object_id('tempdb..##Temptable') IS NOT NULL
DROP TABLE ##Temptable
>
>I tried that in the select into and it still errored out at the call to use it non-dynamically.
>
>Are you saying to create the table non-dynamic, use the dynamic sql to populate it, then use it?
>
>
--sb--