471,089 Members | 1,572 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,089 software developers and data experts.

Return data from multiple tables

Hi there,

I have tables with such structure

transaction_YYMM
(idx,date,company_id,value)

where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [???] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one

best regards
Rafal
May 22 '07 #1
3 5614
I want to define query (maybe view, procedure):
select * from [???] where date>='2007-01-01' and date<='2007-04-30'
A UNION ALL query will combine multiple result sets:

SELECT *
FROM dbo.transaction_0701
UNION ALL
SELECT *
FROM dbo.transaction_0702
UNION ALL
SELECT *
FROM dbo.transaction_0703
UNION ALL
SELECT *
FROM dbo.transaction_0704

You can specify an explicit column list (a Best Practice) and encapsulate
the query in a view to facilitate reuse. You might also consider creating a
partitioned view (or a partitioned table if you are running SQL 2005
Enterprise Edition). See the Books Online for more information.
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rafał Bielecki" <ra***@bielecki.infowrote in message
news:f2**********@nemesis.news.tpi.pl...
Hi there,

I have tables with such structure

transaction_YYMM
(idx,date,company_id,value)

where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [???] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one

best regards
Rafal
May 22 '07 #2

Uzytkownik "Dan Guzman" <gu******@nospam-online.sbcglobal.netnapisal w
wiadomosci news:JO******************@newssvr13.news.prodigy.n et...
A UNION ALL query will combine multiple result sets:

SELECT *
FROM dbo.transaction_0701
UNION ALL
SELECT *
FROM dbo.transaction_0702
UNION ALL
SELECT *
FROM dbo.transaction_0703
UNION ALL
SELECT *
FROM dbo.transaction_0704

You can specify an explicit column list (a Best Practice) and encapsulate
the query in a view to facilitate reuse. You might also consider creating
a partitioned view (or a partitioned table if you are running SQL 2005
Enterprise Edition). See the Books Online for more information.
thank you Dan, your help is very important to me
Rafal
May 22 '07 #3
Rafał Bielecki (ra***@bielecki.info) writes:
I have tables with such structure

transaction_YYMM
(idx,date,company_id,value)

where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [???] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one
In additions to Dan's response, I like to point out that from a logical
point of view, the above design is flawd. It's a lot easier to deal with a
single table. If there are enourmous volumes, it can still be motivated
with partitioning, but then we are talking enourmous values like tens
of millions of rows per month.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 22 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Oksana Yasynska | last post: by
1 post views Thread by Felix_WafyTech | last post: by
4 posts views Thread by HLCruz via AccessMonster.com | last post: by
9 posts views Thread by Anil Gupte | last post: by
9 posts views Thread by karenjfrancis | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.