November 16, 2006 at 5:19 am
Hey All,
I have two tables, namely preferred_supplier and pref_temp. I need to insert and update data into the preferred_supplier table from pref_temp.
There are 4 columns, SITE, ITEM, PREF_SUPPLIER, DESCRIPTION. Now the pref_supplier column has changed with some of the records, and others are new- how must i stucture my script to be able to insert the new values and update the values for pref_supplier to the new suppliers?
Thanks,
Gav
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply