sql server 中用sql实现两个表之间的inert/delete/update
sql 中涉及到两个表CurrentData.dbo.EVUniverse and CurrentData.dbo.Staging_EVUniverse， 从前表到后表操作
;--INSERT NEW RECORDS
INSERT INTO CurrentData.dbo.EVUniverse
SELECT *
FROM CurrentData.dbo.Staging_EVUniverse s
WHERE NOT EXISTS (SELECT 1 FROM CurrentData.dbo.EVUniverse t
WHERE s.UniverseID = t.UniverseID
);
--UPDATE RECORDS THAT EXIST IN BOTH SOURCE AND TARGET
UPDATE t
SET t.UniverseName = s.UniverseName,
t.UniverseGranularityLevel = s.UniverseGranularityLevel,
t.UniverseGroupName = s.UniverseGroupName,
t.Strategy = s.Strategy,
t.LastModifiedStamp = s.LastModifiedStamp,
t.LastUpdateDate = s.LastUpdateDate
FROM CurrentData.dbo.EVUniverse t
JOIN CurrentData.dbo.Staging_EVUniverse s ON s.UniverseID = t.UniverseID
WHERE s.UniverseID IS NOT NULL;
--DELETE RECORDS THAT EXIST IN TARGET TABLE BUT NOT IN SOURCE TABLE.
DELETE FROM CurrentData.dbo.EVUniverse
WHERE NOT EXISTS (SELECT 1 FROM CurrentData.dbo.Staging_EVUniverse s
WHERE s.UniverseID = EVUniverse.UniverseID);
