Create view across multiple databases

Question

I have two databases; 1 is a live database for daily data input and the other is an archival DB for older data.

How can I create a view which gets data from both databases?

Three tables are involve... database1.dbo.table and database1.dbo.tran1 in same database, and database_archived.dbo.table1:

Create VIEW [dbo].[VW_Table_ALL] 
AS 
  SELECT * FROM database1.dbo.table1 
  UNION ALL 
  SELECT * FROM database_archived.dbo.table1 as Data INNER JOIN 
                database1.dbo.tran1 as Tran ON Data.Tran_id = Tran.Tran_Id 

GO

Solution

Not sure if you need a UNION or a JOIN, but in either case you can just use a three-part name for the object in the other database:

USE database1;
GO
CREATE VIEW dbo.MyView
AS
    SELECT columns FROM dbo.LocalTable
    UNION ALL
    SELECT columns FROM database2.dbo.RemoteTable;
GO