November 16, 2006 at 5:55 am
Assuming the PK is (Site, Item), try something like the following:
-- Insert
INSERT INTO preferred_supplier
SELECT SITE, ITEM, PREF_SUPPLIER, [DESCRIPTION]
FROM pref_temp T
WHERE NOT EXISTS (
SELECT *
FROM preferred_supplier S
WHERE S.Site = T.Site AND S.Item = T.Item )
-- TSQL Update
UPDATE S
SET PREF_SUPPLIER = T.PREF_SUPPLIER
,[DESCRIPTION] = T.[DESCRIPTION]
FROM preferred_supplier S
JOIN pref_temp ON S.Site = T.Site AND S.Item = T.Item
WHERE S.PREF_SUPPLIER <> T.PREF_SUPPLIER
OR S.[DESCRIPTION] <> T.[DESCRIPTION]
-- ANSI SQL Update (standard alternative to the above.)
UPDATE preferred_supplier
SET PREF_SUPPLIER = (
SELECT T1.PREF_SUPPLIER
FROM pref_temp T1
WHERE preferred_supplier.Site = T1.Site AND preferred_supplier.Item = T1.Item
AND (preferred_supplier.PREF_SUPPLIER <> T.PREF_SUPPLIER
OR preferred_supplier.[DESCRIPTION] <> T.[DESCRIPTION]) )
,[DESCRIPTION] = (
SELECT T2.[DESCRIPTION]
FROM pref_temp T2
WHERE preferred_supplier.Site = T2.Site AND preferred_supplier.Item = T2.Item
AND (preferred_supplier.PREF_SUPPLIER <> T.PREF_SUPPLIER
OR preferred_supplier.[DESCRIPTION] <> T.[DESCRIPTION]) )
WHERE EXISTS (
SELECT *
FROM pref_temp T
WHERE preferred_supplier.Site = T.Site AND preferred_supplier.Item = T.Item
AND (preferred_supplier.PREF_SUPPLIER <> T.PREF_SUPPLIER
OR preferred_supplier.[DESCRIPTION] <> T.[DESCRIPTION]) )
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply