1

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?

0

1 Answer 1

4

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
1
  • 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
    Commented Jan 7, 2013 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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