Post reply

Help with linked server query

  • August 12, 2010 at 3:45 pm

    #225827

    Hi All,

    I need some help with a query. I want to make a query that uses a linked server table and a local table if possible.

    Below is a query from access, it works but I want to move it to our SQL Server because it will execute faster. Access is VERY slow.

    ACCESS QUERY

    SELECT dbo_MY_LINKED_TABLE. Item1, dbo_MY_LINKED_TABLE. Item2, dbo_MY_LINKED_TABLE. Item3, dbo_MY_LINKED_TABLE. Item4, dbo_MY_LINKED_TABLE. Item5

    FROM dbo_MY_LINKED_TABLE INNER JOIN MY_LOCAL_TABLE ON dbo_MY_LINKED_TABLE. Item1 = MY_LOCAL_TABLE.Item1;

    Below is my linked server query that returns all rows, how can I set up a join with MY_LOCAL_TABLE.Item1 to MY_LINKED_TABLE.Item_1? I tried making a view with the SQL code below but the server didn't like it, it would not save.

    SELECT *

    FROM OPENQUERY(MY_LINKED_SERVER, '

    SELECT MY_LINKED_TABLE.Item_1, MY_LINKED_TABLE.Item_2, MY_LINKED_TABLE.Item_3, MY_LINKED_TABLE.Item_4, tblMyTbl.Item_5

    FROM MY_LINKED_TABLE ');

    TIA

    A

  • August 13, 2010 at 12:46 am

    #1206418

    Hi,

    you can join a local table with a table. Either you can:

    SELECT *

    FROM LocalTable Local

    INNER JOIN MyLinkedServer.MyDatabase.MySchema.LinkedTable Linked

    ON Local.JoinField = Linked.JoinField

    Or you can use OPENQUERY:

    SELECT *

    FROM LocalTable Local

    INNER JOIN OPENQUERY(MyLinkedServer,

    'SELECT <fieldnames> FROM [[MyDatabase.]MySchema.]LinkedTable WHERE ...') Linked

    ON Local.JoinField = Linked.JoinField

    When join:ing tables on different servers, the Query Optimizer usually have a hard time to optimize the query, so even with small tables you might get very long run times on the query. So often you will get a much better speed by first copy the table from the linked server to a local temporary table and then join:

    SELECT *

    INTO #LinkedTable

    FROM MyLinkedServer.MyDatabase.MySchema.LinkedTable

    /* Alternatively you can use OPENQUERY:

    SELECT *

    INTO #LinkedTable

    FROM OPENQUERY(LinkedServer, 'SELECT <fields> FROM LinkedTable WHERE <filter>') X

    */

    SELECT *

    FROM LocalTable Local

    INNER JOIN #LinkedTable Linked

    ON Local.JoinField = Linked.JoinField

    Good luck!

    /Markus

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply