CODEWITHSUNDEEP
sqlsql-server
Learn AspNet
Learn AspNet

Reputation: 1612

Remove Database Server Join with temp tables

SELECT l.CKey,
       wl.LKey,
       l.AKey, 
       l.LKey
       FROM table1 l 
       INNER JOIN DatabaseServer.Table2 wl 

        ON l.CKey = wl.CKey 
        WHERE  LKey NOT IN (select LKey from DatabaseServer.Table3 wc 
        where wc.LKey = wl.LKey and wc.AKey = l.AKey)

I have this above query and I would like to remove cross database server join with temp tables. Can you please tell me how can I get rid of cross database server joins

Upvotes: 0

Views: 46

Answers (1)

Marek Vitek
Marek Vitek

Reputation: 1633

select *
into temptable
from DatabaseServer.Table2
WHERE DatabaseServer.Table2.Ckey in (select Ckey from table1)


select *
into temptable2
from DatabaseServer.Table3
WHERE DatabaseServer.Table3.AKey not in (select AKey from table1)


SELECT l.CKey,
       wl.LKey,
       l.AKey, 
       l.LKey
       FROM table1 l 
       INNER JOIN temptable wl 

        ON l.CKey = wl.CKey 
        WHERE  LKey NOT IN (select LKey from temptable2 wc 
        where wc.LKey = wl.LKey and wc.AKey = l.AKey)

EDIT: If you need data in temporary tables filtered, then just add WHERE to the SELECT INTO query. The question is how complex the filter will be.

EDIT2: I have added filters that might help with the task of creating temporary tables. Depending on amount of data this will give you, you might want to create indexes on temporary tables before running query itself.

Upvotes: 1

Related Questions