How to create views based on tables from different server?

Question

Hello,

How to create a view from table A from server 1 and table B from server 2, based on a same column named as col? They use different credentials. The servers are SQL Server 2005

Thanks!

Answer 1

Without knowing details, I'm not sure this is the best idea - but this would work for you. It requires four part naming and linked servers.

Here is the syntax for the view.

Create  VIEW [dbo].[vw_CrossServer]
AS

    SELECT * 
    FROM Server1.DatabaseName.Schema.tableA TA
        INNER JOIN Server2.DatabaseName.Schema.tableB TB ON TA.col = TB.col

GO

For this to work, you'll need to setup a linked server between the databases. Linked Server

Link also contains examples and other resources.