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)
精彩评论