Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I have three databases named exayear2011, exayear2012, and exayear2013. They all have the same schema, but the data is different for each year.

I want to create a view that gets data from table1 in all three databases with UNION ALL. How would I do this?

share|improve this question
4  
Just don't try to do this in the view designer - use a query window. Should work fine. – Aaron Bertrand Jan 7 at 21:33

1 Answer

up vote 3 down vote

That isn't that hard to do. A simple view using the three part names of the tables will do it.

CREATE VIEW YourView AS
SELECT Col1, Col2, Col3
FROM exayear2011.dbo.table1
union all
SELECT Col1, Col2, Col3
FROM exayear2012.dbo.table1
union all
SELECT Col1, Col2, Col3
FROM exayear2013.dbo.table1
share|improve this answer
2  
And I don't know if this applies to 2000, but in newer versions, you can include a constant in each part to help the optimizer know which tables to use: SELECT ..., 2011 AS [Year] FROM exayear2011... If you filter on that column when querying the view, SQL Server will only use the appropriate base tables. (I have no idea how this behaves with inequalities or BETWEEN.) – db2 Jan 7 at 21:40
Here, it is just a select query. But he is asking how to create a view. And if it is possible, which database will hold that view? – Praveen Jan 8 at 8:34
2  
A view is just a select statement! You create the view in whichever database you want to use it from. – JamesRyan Jan 8 at 16:35
1  
Happy? Now it's a view. The view can be created in any database that you want. – mrdenny♦ Jan 9 at 0:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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