开发者

sql query logic - sql server 2008

is there a way to improve this query..

INSERT INTO mastertable 
VALUES      (SELECT * 
             FROM   staging_tbl s 
             WHERE  s.pac NOT IN (SELECT pac 
                                  FROM   mastertable) 
                    AND s.store NOT IN (SELECT开发者_如何学Go store 
                                        FROM   mastertable)) 

Not sure if this will work at first place.. Basically..want to select records from Staging_Tbl only if same PAC-STORE combination do not currently exist.. If PAC exist but for another STORE..yes, we should select and vice versa.

For eg: Should if MasterTable is as below,

PAC1  STORE1
PAC1  STORE2
PAC2  STORE1
PAC2  STORE2

I should insert only if there is a record like PAC1 STORE3 in the staging table.. and NOT PAC1 STORE2


Do you have indexes on those columns..that will make a change

you can also use NOT EXISTS

INSERT INTO MASTERTABLE 
 SELECT * FROM Staging_Tbl S 
 WHERE NOT EXISTS ( SELECT 1 FROM MasterTable M 
            WHERE S.STORE = M.STORE
            AND S.PAC = M.PAC)

Or A LEFT JOIN

 INSERT INTO MASTERTABLE 
 SELECT S.* FROM Staging_Tbl S 
 LEFT OUTER JOIN MasterTable M 
    ON S.STORE = M.STORE
    AND S.PAC = M.PAC
WHERE M.PAC IS NULL
AND M.STORE IS NULL

Except, make sure to test performance with this one

INSERT INTO MASTERTABLE 
 SELECT * FROM Staging_Tbl 
 EXCEPT
 SELECT * FROM MASTERTABLE

I myself like NOT EXISTS the best

See also Select all rows from one table that don't exist in another table for usage of OUTER APPLY and EXCEPT to do the same


INSERT MASTERTABLE
SELECT * FROM Staging_Tbl S
WHERE NOT EXISTS
(SELECT 1 FROM MASTERTABLE M
 WHERE M.PAC = S.PAC AND M.STORE = S.STORE)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜