Create view across multiple databases

sql-server, view

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

Problem

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 ```

Original source