Need to Union and Group split databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to Union and Group split databases

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-06-10 : 10:07:05

We had a divestiture within our company. Now what used to be contained in one database in now split into two databases. One showing all history and one being all current data as of 6/1/2008. Is there an easy way to Union or Join these? Right now I'm currently doing a simple UNION ALL, but can't group the two select statements:

SELECT
Year,Location,QtySold
FROM
historydb

UNION ALL

SELECT
Year,Location,QtySold
FROM
currentdb

Can't do a subset and group both of these selects. How would some of you pro's do this? Right now I can put this in a simple view and then create a SP off of this view that would do this grouping, but it seems like I should be able to do it all in one query. Thanks.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-06-10 : 10:18:34

You can use a derived table or CTE:

WITH Everything AS
(
SELECT
Year,Location,QtySold
FROM
historydb

UNION ALL

SELECT
Year,Location,QtySold
FROM
currentdb
)
SELECT whatever FROM Everything
GROUP BY whatever

hope that helps
Bjoern

osupratt
Posting Yak Master

238 Posts

Posted - 2008-06-10 : 10:41:53

Yes it looks good so far. I really appreciate your input and help on this. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources