Search code examples
sql-serverdatabase-administration

how can make relation between 2 table in 2 database in one instance without Link server

I want to connect with between 2 tables in 2 databases without link server.

Solution

  • NOTE: When you say two different databases i'm guessing you mean on two separate servers otherwise just look at 'The beginner''s answer on this.

    You can use FROM OPENDATASOURCE but Microsoft doesn't recommended this as the security details are in the connection string, a Linked Server is the best way to go about it.

    The Microsoft Docs is HERE, and a handy post is HERE.

    The syntax will look something like this:

    SELECT
*
FROM OPENDATASOURCE('Connection String')."DATABASE"."SCHEMA"."TABLE" T1
INNER JOIN Table2 T2 on T1.Id = T2.Id