CODEWITHSUNDEEP
sqlexec
user2433705
user2433705

Reputation: 151

Dynamic SQL - union all tables (number of tables is dynamically created)

I don't know how to union all tables with dynamic SQL. The issue is that I'm inserting into db a number of tables - all having the same structure (only one varchar column 

[Line]

). I don't know that would be the number of tables inserted - it depends on the project. But I want to automate the process in SQL.

I'm using this query to find those tables, additionally I'm adding some [RowNum] that may serve as an ID of each table: 

  SELECT
    ROW_NUMBER() OVER (ORDER BY Name) AS [RowNum],
    [Name] AS [Name]
    INTO #all_tables_with_ids
    FROM #all_tables

This query returns:

    RowNum    |     Name
    ------------------------
      1       |     Table 1
      2       |     Table 2 
      3       |     Table 3 
      4       |     Table 4

I would like to merge all tables together. I was trying to write some insert into in while loop but it didn't work. I figured out that I need dynamic SQL.

Can you suggest something? I was trying to find some examples but all of them fail due to the fact that the list of tables is not known at the beginning, so it needs to be created dynamically as well.

Upvotes: 1

Views: 1250

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

Demo here:

create table #test
(
  RowNum  int,
  Name varchar(100)
  )

insert into #test
select 1,quotename('table1')
union all
select 2,quotename('table2')


declare @sql nvarchar(max)
set @sql='select somecol from tbl union all '

declare @sql1 nvarchar(max)

;with cte
as
(select @sql as ql,name,rplc
from
#test t1
cross apply
(select replace(@sql,'tbl',name) as rplc from #test t2 where t1.rownum=t2.rownum)b
)
select  @sql1= stuff(
(select ''+rplc 
from cte
for xml path('')
),1,0,'')

set @sql1=substring(@sql1,1,len(@sql1)-10)

print @sql1

--exec(@Sql1)

Upvotes: 1

Related Questions