1

I have a database which stores historical data in different tables with same column names. Is there any way I can query all tables and get result

Example

2016_01_01----Fields-f1,f2,f3,f4----Values-1,2,3,4
2016_02_01----Fields-f1,f2,f3,f4----Values-5,6,7,8
2016_12_01----Fields-f1-f2-f3-f4----Values-9,10,11,12

Result should be

f1   f2    f4
1    2     4
5    6     8
9    10    12
4

UNION (Transact-SQL).

Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

select T.f1, T.f2, T.f3
from [2016_01_01] as T
union all
select T.f1, T.f2, T.f3
from [2016_02_01] as T
union all
select T.f1, T.f2, T.f3
from [2016_12_01] as T;

One way to build the statement dynamically.

/*
-- Create tables
create table [2016-01-01](f1 int, f2 int, f3 int);
create table [2016-02-01](f1 int, f2 int, f3 int);
create table [2016-12-01](f1 int, f2 int, f3 int);
*/

declare @SQL nvarchar(max);

set @SQL = stuff((
  select 
N'
union all
select T.f1, T.f2, T.f3 
from ' + quotename(name) + N' as T '
  from sys.tables
  -- Get all tables where the name is a date
  where isdate(name) = 1 
  for xml path(''), type
  ).value('text()[1]', 'nvarchar(max)'), 1, 13, '');

-- Print statement
print @SQL;

-- Execute statment
exec (@SQL);
| improve this answer | |
  • Thanks buddy for your answer, but the thing is that i have 100 tables... :( – Rehan Nov 11 '16 at 15:44
  • 2
    @Rehan Ok, so how is that a problem for you? – Mikael Eriksson Nov 11 '16 at 15:53
  • Hi Mikael, it's not a problem, thing is that I had many columns and many tables so I was feeling little lazy to write all things but I did it using excel Thanks – Rehan Nov 14 '16 at 9:43
  • 1
    @Rehan Yes, why type when you don't have to. Updated the answer with a way to do the same in T-SQL. – Mikael Eriksson Nov 14 '16 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.