How to use dynamic T-SQL to insert values of execute command?

I want to join two tables and get results in a table with parametrized name. I created a procedure which join the two tables . but I don’t know how to insert values to the final table. I wrote following code the @SQL*net code is executed and works fine, but when I add @SQL_*FINAL to insert table into table with parametrized name. I get the error when executing procedure:

Incorrect syntax near the keyword ‘select’.

Incorrect syntax near ‘)’.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Print:

INSERT INTO sqlDB.dbo.leasing_monthly_series exec(select e.symbol,e.tracing_no,e.year
    from sqlDB.dbo.income e 
    inner join sqlDB.dbo.cost g
    on e.tracing_no=g.tracing_no)



ALTER PROCEDURE [dbo].[monthly_series_table] 
@INCOME_SUFFIX VARCHAR(MAX),
@COST_SUFFIX VARCHAR(MAX),
@TABLE_NAME VARCHAR(MAX),
@SECTION BIGINT

AS
BEGIN

    SET NOCOUNT ON;
    Declare @SQL_net varchar(max) =''
IF OBJECT_ID('sqlDB.dbo.income', 'U') IS NOT NULL
    DROP TABLE income; 
BEGIN 
    CREATE TABLE income (
                symbol varchar(max),
                tracing_no bigint,
                year bigint

        )
END


IF OBJECT_ID('sqlDB.dbo.cost', 'U') IS NOT NULL
    DROP TABLE cost; 
BEGIN 

    CREATE TABLE cost (
                symbol varchar(max),
                tracing_no bigint,
                year bigint

        )
END
DECLARE @SQL VARCHAR(MAX)=''
DECLARE @SQL_FINAL VARCHAR(MAX)=''

exec sqlDB.dbo.drop_table_exists @TABLE_NAME
BEGIN 
set @SQL='
    CREATE TABLE sqlDB.dbo.'+ @TABLE_NAME+' (
                symbol varchar(max),
                tracing_no bigint,
                year bigint

        )'
        
END
EXEC (@SQL)
INSERT INTO income
EXEC sqlDB.dbo.monthly_series @INCOME_SUFFIX,'cum_income','this_month_income',@SECTION

INSERT INTO cost
EXEC sqlDB.dbo.monthly_series @COST_SUFFIX,'cum_cost','this_month_cost',@SECTION

set @SQL_net='select e.symbol,e.tracing_no,e.year
    from sqlDB.dbo.income e 
    inner join sqlDB.dbo.cost g
    on e.tracing_no=g.tracing_no'

SET @SQL_FINAL='INSERT INTO sqlDB.dbo.'
+@TABLE_NAME+' exec('+@SQL_net+')'
Print @SQL_Final
exec (@SQL_FINAL)

END

>Solution :

You need to get rid of the exec for the select statement.

You can test your output of @sql_final best in SSMS for SQL Server

So your code would be

   SET @SQL_net = 'select e.symbol,e.tracing_no,e.year
       from sqlDB.dbo.income e 
       inner join sqlDB.dbo.cost g
       on e.tracing_no=g.tracing_no'
   
   SET @SQL_FINAL = 'INSERT INTO sqlDB.dbo.'
   + @TABLE_NAME + ' ' + @SQL_net
   PRINT @SQL_Final
   EXEC (@SQL_FINAL)