CODEWITHSUNDEEP
sqlsql-server
OwlsSleeping
OwlsSleeping

Reputation: 1570

SQL- UNION ALL a large number of tables

I have a large number of tables (some thousands) containing similar data. I would like to run some reports from these. The table names are similar, so I can get a list of table names.

I will likely merge these tables in the future, should be trivial once the select works.

--Getting a list of all tables 
select TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TableNamePrefix%'
ORDER BY TABLE_NAME

To combine data, I can use UNION ALL, but not sure about how to set up the WHILE/cursor so that the list does not need to be constantly updated.

Something like

SELECT * FROM TableNamePrefix00001
UNION ALL
SELECT * FROM TableNamePrefix00002
UNION ALL
SELECT * FROM TableNamePrefix00003
--And so on for all tables in the list

Any help appreciated, thanks.

Upvotes: 3

Views: 8071

Answers (4)

Cato
Cato

Reputation: 3701

using your pattern on table name - i got somewhere with

DECLARE @SQL nvarchar(max);

select @SQL =  COALESCE(@SQL , '') + 'SELECT * FROM [' +  TABLE_NAME + ']  UNION ALL ' 
FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME LIKE '%employeedet%';

SELECT @SQL = LEFT(@SQL, LEN(@SQL) - 11);

print @SQL;

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

select 'select * from  '+TABLE_NAME +' union all'
FROM
 INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%chd%'
ORDER BY TABLE_NAME

remove last union all

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

You can do this with Dynamic SQL

Declare @SQL varchar(max) =''
Select @SQL = @SQL +'Union All Select * From '+Table_Name+' ' 
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_NAME LIKE 'TableNamePrefix%'
  ORDER BY TABLE_NAME
Set @SQL = Stuff(@SQL,1,10,'')
Exec(@SQL)

Upvotes: 6

Geovanny Hernandez
Geovanny Hernandez

Reputation: 237

All these tables have the same data types and number of columns, because if this not the case then you will not be able to use UNION sentences, however, it's very strange to merge all the information in this way, could you put some examples to clarify it.

Upvotes: -1

Related Questions