-1

I am using a dynamic query in SQL which returns me a large data set which I need to insert into a temporary table.

Does SELECT INTO #tmp from EXEC sp_executesql @query work?

I did try it out but I get an error 'Incorrect syntax near the keyword 'EXEC'

I know that it works using Insert into, but I don't want to create the table manually as it is a large table with a lot of columns. Is there an alternative without having to creating the temp table manually?

TheFallenOne
  • 1,598
  • 2
  • 23
  • 57
  • 1
    possible duplicate of [Insert results of a Stored Procedure into a Temporary Table](http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) – Siyual Jul 10 '15 at 15:49
  • @Siyual I did notice that, but it looked too complicated for just a simple insert into a table. – TheFallenOne Jul 10 '15 at 15:55
  • Could you insert select into statement into your stores proc ? – Marcin Jul 10 '15 at 15:58
  • @Marcin I am not clear as to what you mean. But SELECT * INTO #tmp FROM TABLE does work in a procedure – TheFallenOne Jul 10 '15 at 17:56
  • I mean is It possible to insert select into in a front of your dynamic query. Than you could only execute dynamic qyery, which include select into statement – Marcin Jul 10 '15 at 18:28

2 Answers2

0

No it does not workb you cannot do this like that. However the work around is this, before the dynamic SQL create a temp table like normal, and in the dynamic SQL insert into that table. Then when you are done you will have the table filled.

Holmes IV
  • 1,673
  • 2
  • 23
  • 47
  • Yeah, I did realize it. Can't think of any other option other than creating a temp table of a 100 columns. – TheFallenOne Jul 10 '15 at 17:58
  • I suppose you could try to do a single select into hardcoded to create the temp table, not sure the code or if that is even possible. But if so select into 1 row, then truncate it and follow with the dynamic – Holmes IV Jul 10 '15 at 18:30
0
IF OBJECT_ID('tempdb..##TmepTable') IS NOT NULL DROP TABLE ##TmepTable
CREATE TABLE ##TmepTable (TmpCol CHAR(1))
DECLARE @SQL NVARCHAR(max) =' IF OBJECT_ID(''tempdb..##TmepTable'') IS NOT NULL DROP TABLE ##TmepTable 
                        SELECT * INTO ##TmepTable  from [MyTableName]'
EXEC sp_executesql @SQL

SELECT Alias.* FROM  ##TmepTable as Alias
IF OBJECT_ID('tempdb..##TmepTable') IS NOT NULL DROP TABLE ##TmepTable
user1216435
  • 11
  • 3