sql server 中用sql实现两个表之间的inert/delete/update

最新推荐文章于 2022-09-17 12:08:45 发布

原创最新推荐文章于 2022-09-17 12:08:45 发布 · 134 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL Server

DB 专栏收录该内容

7 篇文章

订阅专栏

本文介绍如何使用SQL将数据从一个表复制到另一个表，并更新或删除存在重复的数据。具体操作包括插入新记录、更新现有记录以及删除不在目标表中的记录。

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);