0

I'm trying to load a dynamic table name in to a local table. Seems simple enough from the examples I've found, however I'm getting an error message. -> Incorrect syntax near '#outtbl_15133897'

Hopefully, another set of eyes can see what I'm missing. Thanks

DECLARE @OutTbl TABLE ( Name varchar(100), type varchar(20), row int );
DECLARE @curName as NVARCHAR(MAX);

DECLARE @sqlCommand as NVARCHAR(MAX);
SET @curName = '#outtbl_' + LEFT(replace(replace(CONVERT (time, GETDATE()),':',''),'.',''),8);

SET @sqlCommand = 'CREATE TABLE #OutTbl ( Name varchar(100), type varchar(20), row int ); '
+ 'INSERT INTO #outtbl SELECT c.Name,c.Type, ROW_NUMBER() OVER(ORDER BY c.QueryID,c.GroupID,c.ColumnID) as row '
+ 'FROM MYDB.dbo.DynamicReport_Columns c '
+ 'INNER JOIN MYDB.dbo.DynamicReport_Tables t on t.TableID = c.TableID '
+ 'WHERE c.QueryID=1 and c.GroupID=1 and IsOutput <> ''N'';';
SET @curName = @curName + ' TABLE OUTPUT';
EXEC sys.sp_executesql @sqlCommand,@curName,@OutTbl output
Galactic
  • 400
  • 4
  • 14

1 Answers1

-1

I'm not 100% on this... I think I had a similar issue, but I solved it by making the temp table a global temp table... ##outtbl

manderson
  • 837
  • 1
  • 6
  • 18
  • I would avoid global temp tables . They have some problems ,. They are available to any connection so concurrency is a nightmare – Shachaf.Gortler Mar 03 '16 at 22:13
  • Offer a suggestion... because I would also like to know a more efficient way of doing this. I looked up the procedure I wrote that uses the global temp table and switched it out for a local temp table. I received the same error as above, but works with global temp table. – manderson Mar 04 '16 at 14:12
  • Here is some more info on the above problem. http://stackoverflow.com/questions/662049/dynamic-sql-results-into-temp-table-in-sql-stored-procedure – manderson Mar 04 '16 at 14:13