I have the following query:
DECLARE @QUERY NVARCHAR(400)='
INSERT INTO #AA(ID)
SELECT ID FROM BB
'
DECLARE @CREATETABLE NVARCHAR(400)='
IF OBJECT_ID(''tempdb..#AA'') IS NOT NULL DROP TABLE #AA
CREATE TABLE #AA
(
ID INT
)
'
EXEC sp_ExecuteSql @CREATETABLE
EXEC sp_ExecuteSql @QUERY
I need to get the table #AA
with a SELECT.
You can't at least with temporary tables. A type table
#tabla
is only visible from the session in which it was created. In your example, the temporary one is created in the sp_ExecuteSql context, so when you exit it, the table ceases to exist. As far as I know the only way to solve it is toCREATE
do it outside the scope ofsp_ExecuteSql
, for example:Note : At least in SQL Server
Here is the example to get the temporary table with data:
DECLARE @QryToFillTempTable AS NVARCHAR(MAX)
Set @QryParaFillTemporalTable = ' INSERT INTO ##A(ID) SELECT Id FROM Requirement '
EXEC sp_executesql @QryToFillTemporalTable
SELECT * FROM ##A