如果你在一个不支持MERGE语句的数据库中,避免从源表插入重复记录的另一种方法是只使用UPDATE和INSERT语句:
CREATE PROC [DW].[Load_Quest_Staff_Curricula_D] AS
INSERT INTO DW.Quest_Staff_Curricula_D
(
User_Id,
Full_Name,
Job_Type_Name,
First_Name,
Middle_Name,
Last_Name,
Office_Name,
Office_Num,
Start_Date,
Date_Terminated,
Curriculum_Name,
Curriculum_Status,
IsCurrent,
DateCreated,
DateModified
)
SELECT
s.Person_AD_Name AS User_Id,
s.Person_Full_Name AS Full_Name,
s.Person_Job_Type_Name AS Job_Type_Name,
s.Person_First_Name AS First_Name,
s.Person_Middle_Name AS Middle_Name,
s.Person_Last_Name AS Last_Name,
s.Person_Office_Name AS Office_Name,
s.Person_Office_Num AS Office_Num,
s.Person_Start_Date AS Start_Date,
s.Person_Date_Terminated AS Date_Terminated,
s.Curriculum_Name AS Curriculum_Name,
s.Student_Curriculum_Status AS Curriculum_Status,
1 AS IsCurrent,
GETDATE() AS DateCreated,
GETDATE() AS DateModified
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Person_AD_Name ORDER BY Person_AD_Name) AS Row
FROM SABA.SABA_Curricula
) as s
LEFT JOIN DW.Quest_Staff_Curricula_D T
ON T.User_Id = s.Person_AD_Name
WHERE T.User_Id is NULL
AND row = 1
UPDATE T
SET T.User_Id = s.Person_AD_Name,
T.Full_Name = s.Person_Full_Name,
T.Job_Type_Name = s.Person_Job_Type_Name,
T.First_Name = s.Person_First_Name,
T.Middle_Name = s.Person_Middle_Name,
T.Last_Name = s.Person_Last_Name,
T.Office_Name = s.Person_Office_Name,
T.Office_Num = s.Person_Office_Num,
T.Start_Date = s.Person_Start_Date,
T.Date_Terminated = s.Person_Date_Terminated,
T.Curriculum_Name = s.Curriculum_Name,
T.Curriculum_Status = s.Student_Curriculum_Status,
T.DateModified = GETDATE()
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Person_AD_Name ORDER BY Person_AD_Name) AS Row
FROM SABA.SABA_Curricula
) as s
LEFT JOIN DW.Quest_Staff_Curricula_D T
ON T.User_Id = s.Person_AD_Name
WHERE T.User_ID IS NOT NULL
AND row = 1
UPDATE DW.Quest_Staff_Curricula_D
SET IsCurrent = 0
FROM DW.Quest_Staff_Curricula_D T
LEFT JOIN (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Person_AD_Name ORDER BY Person_AD_Name) AS Row
FROM SABA.SABA_Curricula
) AS s
ON T.User_Id = s.Person_AD_Name
WHERE row = 1
AND T.User_ID IS NULL
AND isCurrent = 1
希望这能有所帮助。