I get the error

A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable

when I run the following lines of code on Microsoft SQL Server 2016. I am trying to use these codes to query multiple tables (with suffix after "EY_RCMTxn_20") from the server and then name the consolidated results as #RCMTxn. I am able to query multiple tables, but fail to name it #RCMTxn.

How can I resolve this error?

DROP TABLE IF EXISTS #RCMTxn

DECLARE @sql VARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql  '
    UNION ALL
    SELECT * FROM [' name ']'
INTO #RCMTxn
FROM sys.tables
WHERE name LIKE '%EY_RCMTxn_20%'
SET @sql = STUFF(@sql,1,15,'')
EXEC(@sql)

CodePudding user response：

Can you split your work into more steps? Like this:

DROP TABLE IF EXISTS #RCMTxn

DECLARE @sql VARCHAR(MAX);

SET @sql = ''
SELECT @sql = @sql  '
    UNION ALL
    SELECT * FROM [' name ']'
FROM sys.tables
WHERE name LIKE '%EY_RCMTxn_20%';

SET @sql = STUFF(@sql,1,15,'')

SELECT @sql as sql
INTO #RCMTxn;

EXEC(@sql);

CodePudding user response：

Looks like you wanted the INTO inside the dynamic SQL.

Also:

• You need to escape the table names properly
• Use nvarchar(max) for dynamic SQL
• Do not use SET @var = to aggregated. Use STRING_AGG or FOR XML to aggregate.

DROP TABLE IF EXISTS #RCMTxn;

DECLARE @sql nvarchar(max) = '
SELECT *
INTO #RCMTxn
FROM (
'  
(
    SELECT STRING_AGG(
'SELECT * FROM '   QUOTENAME(name), '
UNION ALL
'      )
    FROM sys.tables
    WHERE name LIKE '%EY_RCMTxn_20%'
)   '
) t;
';

EXEC sp_executesql @sql;

For older versions of SQL Server, you can use this:

DROP TABLE IF EXISTS #RCMTxn;

DECLARE @sep nvarchar(100) = '
UNION ALL
';

DECLARE @sql nvarchar(max) = '
SELECT *
INTO #RCMTxn
FROM (
'  
STUFF((
    SELECT @sep   'SELECT * FROM '   QUOTENAME(name)   '
'
    FROM sys.tables
    WHERE name LIKE '%EY_RCMTxn_20%'
    FOR XML PATH(''), TYPE
  ).value('text()[1]','nvarchar(max)'), 1, LEN(@sep), '')
  '
) t;
';

EXEC sp_executesql @sql;

Note that whatever it is that you are doing with temp tables could probably be done without.