MS SQL Server - Linked table declare linked connection upfront

Question

I have a linked server setup and need to pull data from the linked server into my main server. It feels painful to do using the whole server name. I'm trying to see if I can establish the name upfront then use in the rest of my query below.
Such as this:

Linkedserver.Database.dbo.Table as Link1

then below in my queries be able to have this work:

select * from Link1

or

select * from main-server left join Link1

That isn't how SQL works. You can alias an object in the query, but SQL isn't a programming language, you can't declare a reference and then use that for the remainder of the batch. if you find you're referencing a specific object frequently, take a look at Synonyms. — Thom A
– Thom A ♦, Commented Dec 6, 2019 at 16:13
Thank you for this. I've used views in the past, it is a synonym that I was looking for! That solved my issue, thanks for everyone's input! — grs
– grs, Commented Dec 9, 2019 at 15:54

Gordon Linoff · Accepted Answer · 2019-12-06 16:30:30Z

1

One method is a view, which does pretty much exactly what you want:

create view v_linked
    select *
    from Linkedserver.Database.dbo.Table;

You can then reference this as:

select *
from v_linked;

Synonyms are an alternative mechanism:

create synonym linked for Linkedserver.Database.dbo.Table;

There are few practical differences between synonyms and views. However, there are some, such as the effect on existing objects when one is deleted and the DDL triggers needed to track changes.

edited Dec 6, 2019 at 16:30

answered Dec 6, 2019 at 16:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Thom A Over a year ago

Why a VIEW rather than a SYNONYM?

Gordon Linoff Over a year ago

@Larnu . . . (1) Views are compatible with any database. (2) Views are likely to be more familiar to someone who knows SQL but not the intricacies of SQL Server. (3) The answer isn't intended to show a preference for one over the other.

Thom A Over a year ago

My comment was written before you had added the extra parts on Synonyms. Hence why I asked as the time, as a Synonym (to myself at least) seem liked a more logical choice over a VIEW in SQL Server.

Collectives™ on Stack Overflow

MS SQL Server - Linked table declare linked connection upfront

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related