Reputation: 1657
Was trying to select...into a temp Table #TempTable in sp_Executedsql. Not its successfully inserted or not but there Messages there written (359 row(s) affected) that mean successful inserted? Script below
DECLARE @Sql NVARCHAR(MAX);
SET @Sql = 'select distinct Coloum1,Coloum2 into #TempTable
from SPCTable with(nolock)
where Convert(varchar(10), Date_Tm, 120) Between @Date_From And @Date_To';
SET @Sql = 'DECLARE @Date_From VARCHAR(10);
DECLARE @Date_To VARCHAR(10);
SET @Date_From = '''+CONVERT(VARCHAR(10),DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-1,120)+''';
SET @Date_To = '''+CONVERT(VARCHAR(10),DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-1,120)+''';
'+ @Sql;
EXECUTE sp_executesql @Sql;
After executed,its return me on messages (359 row(s) affected). Next when trying to select out the data from #TempTable.
Select * From #TempTable;
Its return me:
Msg 208, Level 16, State 0, Line 2
Invalid object name '#TempTable'.
Suspected its working only the 'select' section only. The insert is not working. how fix it?
Upvotes: 55
Views: 109936
Reputation: 4844
Using a global temporary table in this scenario could cause problems as the table would exist between sessions and may result in some problems using the calling code asynchronously.
A local temporary table can be used if it defined before calling sp_executesql e.g.
CREATE TABLE #tempTable(id int);
execute sp_executesql N'INSERT INTO #tempTable SELECT myId FROM myTable';
SELECT * FROM #tempTable;
Upvotes: 56
Reputation: 6095
Note, from T-SQL 2021 onwards, dm_exec_describe_first_result_set() can be used to build a temporary table in the right shape to INSERT INTO - as it gives you the column names and types that will be returned from your dynamic SELECT or EXEC ... so you can build dynamic SQL to ALTER a temporary table into the shape you need.
DECLARE @strSQL NVarChar(max) = 'EXEC [YourSP] @dtAsAt=''2022-11-09'', @intParameter2=42'
--*** Build temporary table: create it with dummy column, add columns dynamically
--*** using an exec of sys.dm_exec_describe_first_result_set() and dropping the dummy column
DROP TABLE IF EXISTS #tblResults;
CREATE TABLE #tblResults ([zz] INT);
DECLARE @strUpdateSQL NVarChar(max);
SELECT @strUpdateSQL = STRING_AGG( CONCAT( 'ALTER TABLE #tblResults ADD ',
QUOTENAME([name]), ' ',
[system_type_name], ';')
, ' ') WITHIN GROUP (ORDER BY [column_ordinal])
FROM sys.dm_exec_describe_first_result_set (@strSQL, NULL, 0)
SET @strUpdateSQL += 'ALTER TABLE #tblResults DROP COLUMN [zz];'
EXEC (@strUpdateSQL);
--*** Now we have #tblResults in the right shape to insert into, and use afterwards
INSERT INTO #tblResults EXEC (@strSQL);
SELECT * FROM #tblResults;
--*** And tidy up
DROP TABLE IF EXISTS #tblResults;
Upvotes: 5
Reputation: 1
This one worked for me:
DECLARE @Query as NVARCHAR(MAX);
SET @Query=(SELECT * FROM MyTable) ;
SET @Query=(SELECT 'SELECT * INTO dbo.TempTable FROM ('+@Query +') MAIN;');
EXEC sp_executesql @Query;
SELECT * INTO #TempTable FROM dbo.TempTable;
DROP TABLE dbo.TempTable;
SELECT * FROM #TempTable;
Upvotes: 0
Reputation: 681
In your @sql
string, don't insert into #TempTable
. Instead, call your SELECT
statement without an INSERT
statement.
Finally, insert the results into your temporary table like so:
INSERT INTO @tmpTbl EXEC sp_executesql @sql
Also, you'll need to declare the temporary table if you use this approach
DECLARE @tmpTbl TABLE (
//define columns here...
)
Upvotes: 20
Reputation: 61
This worked for me
declare @sql nvarchar(max)
create table #temp ( listId int, Name nvarchar(200))
set @sql = 'SELECT top 10 ListId, Name FROM [V12-ListSelector].[dbo].[List]'
insert into #temp
exec sp_executesql @sql
select * from #temp
drop table #temp
Upvotes: 2
Reputation: 21
declare @sql varchar(1000)
set @sql="select * into #t from table;"
set @sql =@sql + "select * from #t;"
execute SP_EXECUTESQL @sql
Upvotes: 2
Reputation: 550
To work around this issue use a CREATE TABLE #TEMPTABLE command first to generate an empty temp table before running sp_executesql. Then run the INSERT INTO #TEMPTABLE with sp_executesql. This will work. This is how I overcome this problem as I have a setup in which all my queries are usually run via sp_executesql.
Upvotes: 1
Reputation: 23183
Local temporary table #table_name
is visible in current session only, global temporary ##table_name
tables are visible in all sessions. Both lives until their session is closed.
sp_executesql
- creates its own session (maybe word "scope" would be better) so that's why it happens.
Upvotes: 42
Reputation: 2343
Temporary tables only live as long as the connection that creates them. I would expect that you're unintentionally issuing the select on a separate connection. You can test this by momentarily doing your insert into a non-temporary table and seeing if your data is there. If that is the case you can go back to your original solution and just be sure to pass the connection object to your select.
Upvotes: 3
Reputation: 15685
your temp table in dynamic SQL is out of scope in the non dynamic SQL part.
Look here how to deal with this: A bit about sql server's local temp tables
Upvotes: 7