Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 16 days ago.
Improve this question

I have a primary table in my db as below

Also I have 6 tables in my db namely Table_1, Table_2, Table_3, Table_4, Table_5 and Table_6 (with all same columns) . Example shown below

Now, I want to combine tables where i have a condition. Condition is whenever primary_table has a consider 'Yes', only then that number is considered. In this example 2,3 and 4 from primary_table (number column) are considered so Table_2,Table_3 and Table_4 are taken and combined.

I have a written a query(say Q1.sql) for this and this works fine. But the issue is after I execute Q1.sql, i need to copy paste that contents into another window and then execute.

Q1.sql below

SET NOCOUNT ON;
DECLARE
@table_name nvarchar(max) = 'TABLE_'

DECLARE @table_variable table (id int identity(1,1), num int)

insert into
@table_variable
select number from primary_table where consider in ('Yes')

declare @intflag int,
@nrows int,
@tab1 nvarchar(max),
@dec_var nvarchar(max) = '',
@uni nvarchar(max) = ''

select @nrows = count(number) from primary_table where consider in ('Yes')
set @intflag = 0 
while (@intflag <= @nrows)

begin 

select @tab1 = concat(@dec_var, Char(13), N' if (Exists (
Select * from information_schema.tables where TABLE_NAME in (''',@table_name,num,'''))) BEGIN insert into @tabs select col1 from ',@table_name,num, ' END', CHAR(13), @uni)
from @table_variable where id = @intflag
   set @intflag = @intflag + 1
   set @uni = case
              when @intflag < @nrows then ''
              when @intflag >= @nrows then 'select * from @tabs'
              end 
              set @dec_var = case 
                        when @intflag = 1 then 'Declare @tabs table (col1 int)'
                        when @intflag >= @nrows then ''
                        end 
              print (@tab1)

END 
GO 
SET NOCOUNT OFF;

After I execute above , i get below

Declare @tabs table (col1 int)
 if (Exists (
Select * from information_schema.tables where TABLE_NAME in ('TABLE_2'))) BEGIN insert into @tabs select col1 from TABLE_2 END

 if (Exists (
Select * from information_schema.tables where TABLE_NAME in ('TABLE_3'))) BEGIN insert into @tabs select col1 from TABLE_3 END

 if (Exists (
Select * from information_schema.tables where TABLE_NAME in ('TABLE_4'))) BEGIN insert into @tabs select col1 from TABLE_4 END
select * from @tabs

Then I Need to copy the above contents in another window and then execute. My question is , the moment I execute Q1.sql, I need to get the output (there is no need to copy the contents in another window). However I tried to replace print (@tab1) with exec(@tab1) ,but getting error .

Can anyone help? Not sure what wrong I am doing here