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
– M8CC1

2013-07-30 16:04:34 +00:00
Commented Jul 30, 2013 at 16:04 — M8CC1
– M8CC1, Commented Jul 30, 2013 at 16:04

Aaron Bertrand · Accepted Answer · 2013-07-30 15:34:41Z

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, 2013 at 15:34

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Igbanam Over a year ago

is it possible to do this across MySQL hosts? And how could I like those two database servers in the view?

Louis Loudog Trottier Over a year ago

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.

Collectives™ on Stack Overflow

Create view across multiple databases

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related