创建跨多个数据库的视图 [英] Create view across multiple databases

问题描述

我有两个数据库；一个是用于日常数据输入的实时数据库，另一个是用于旧数据的归档数据库.

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?

三个表涉及... database1.dbo.table 和 database1.dbo.tran1 在同一个数据库中，database_archived.dbo.table1:

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

推荐答案

不确定是否需要 UNION 或 JOIN，但在任何一种情况下，您都可以为另一个数据库中的对象使用由三部分组成的名称:

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

这篇关于创建跨多个数据库的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！