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

Is my view able to create based on written above? – M8CC1 Jul 30 '13 at 16:04 — M8CC1, Jul 30 '13 at 16:04

score 7 · Answer 1 · answered Jul 30 '13 at 15:34

7

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

answered Jul 30 '13 at 15:34

Aaron Bertrand

272,866
37
466
490

1

is it possible to do this across MySQL hosts? And how could I like those two database servers in the view? – Igbanam Jul 15 '15 at 14:13
I have the almost the same scenario but different user on the same host. how is that doable? Google tells me to merge all the db in one, but it's not viable in my situation. – Louis Loudog Trottier Feb 04 '17 at 08:57

Create view across multiple databases

1 Answers1

Linked