Select INTO Temp table from dynamic sql string variable

The only way I can think of you could achieve this would be to persist an object in tempdb. Temporary tables only persist for the session they are created in, meaning that if you create a temporary table using dynamic SQL, isn't only persist for that session in sp_executesql.

EXEC sp_executesql N'SELECT 1 AS one INTO #test;';

--This'll fail
SELECT * FROM #test;

Therefore you'll need to use a persisted table in tempdb:

DECLARE @NyCol sysname,
        @MyTable sysname,
        @MySchema sysname;
--Assume these are set somewhere

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'SELECT ' + QUOTENAME(@MyCol) + @CRLF +
           N'INTO tempdb.dbo.MyTempTable'  + @CRLF +
           N'FROM ' + QUOTENAME(@MySchema) + N'.' + QUOTENAME(@MyTable) + N';';

EXEC sp_executesql @SQL;

--Do Stuff

--Clean up

DROP TABLE tempdb.dbo.MyTempTable;

There are two ways that I've used.

If the structure of your temp table is known in advance, you can do this:

CREATE TABLE #temp (<column list>);

SET @my_sql = <your query syntax, without the INTO clause>;

INSERT #temp (<column list>)
EXECUTE sp_executesql @my_sql;

If, on the other hand, your table structure is unknown in advance, you can use a global temp table (##temp) instead of a local temp table (#temp):

EXECUTE sp_executesql <your query with INTO ##temp etc>

The global table (##temp) will be accessible from the procedure outside the EXECUTE statement.

Scope of the temporary tables are only limited till the session is alive.

In your case, when you use sp_executesql, the local temp table is out of scope once the call to sp_executesql is finished.

You need to create global temp tables.

See below a small example:

create table dbo.test_mytable( col1 int );
GO

insert into dbo.test_mytable
select 1 union
select 2;
go

create or alter procedure dbo.test_myproc( @mytable varchar(255), @mycol varchar(255) )
as
begin
  declare @mysql varchar(4000);

  drop table if exists ##mytemptable;
  set @mysql = 'select ' + @mycol + ' from ' + @mytable;
  set @mysql = 'select * into ##mytemptable from (' + @mysql + ')  X';

  exec (@mysql);
  select * from ##mytemptable;
end;
GO

exec dbo.test_myproc 'dbo.test_mytable', 'col1';

When you run the above code snippet, you will see the result as below:

col1
1
2