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 #AA table with a SELECT.
You can not at least with temporary tables. A table type #tabla is only visible from the session in which it was created, in your example, the temporary is created in the context of sp_ExecuteSql, so when you leave it, the table ceases to exist. That I know the only way to solve it is that the CREATE is done outside the scope of sp_ExecuteSql , for example:
DECLARE @QUERY NVARCHAR(400)='
INSERT INTO #AA(ID)
SELECT 1
'
CREATE TABLE #AA (ID INT)
EXEC sp_ExecuteSql @QUERY
SELECT * FROM #AA
Note : At least in SQL Server
Here is the example to get the temporary table with data:
DECLARE @QryParaFillTablaTemporal AS NVARCHAR (MAX)
Set @QryParaLetterTablaTemporal = 'INSERT INTO ## A (ID) SELECT ID FROM Requirement '
EXEC sp_executesql @QryParaLearningTablaTemporal
SELECT * FROM ## A
