sql – union tables with same prefix

I have a set of tables with same prefix and same structure. All I need is to “combine” them as one.

use thisdb
declare @maxrow int, @result nvarchar(4000), @tempname nvarchar(20)
set @maxrow = (select count(*) from information_schema.tables where table_schema = 'dbo' and table_name like N'AAbc%')
set @result = ''
set @tempname = 'mytemp'
select @result = @result + case when [row] = 1 then replace([name],'from',concat('into ##',@tempname, ' from')) when [row] = @maxrow then replace([name],'union all','') else [name] end + ' ' 
from 
(select ['table_name,'] union all') as [name], row_number() over(order by table_name asc) as [Row]
from information_schema.tables
where table_schema = 'dbo' and table_name like N'AAbc%') x
execute sp_executesql @result

Basically I retrieve tables with certain pattern from information_schema.tables, then get rid of the last union all.

Above method works for 20-30 tables, as the @result won’t exceed the limit for nvarchar. But I’m curious how to get this job done if number of tables N is very large? Or there’s better way to deal with this problem?

Answer

This should suffice. “Union all” replaced with “Go”.

declare @script nvarchar(max)

set @script = (
SELECT STUFF(( SELECT  'insert #MyTable(sharedColName1, SharedColName2) select sharedColName1, SharedColName2 from ' + Table_Name + ' Go '
                FROM    ( SELECT DISTINCT
                                    Table_Name
                          FROM      INFORMATION_SCHEMA.Tables
                          where table_Name Like '<prefix_for_tables_with_identical_names>%'
                        ) x
              FOR
                XML PATH('')
              ), 1, 0, '') A
              )
sp_executeSql @script