TSQL合并具有多种来源(TSQL MERGE with multiple sources)

本来我想用if / else语句来完成的“UPSERT”，有人建议我用MERGE我的问题与MERGE是它看起来并不像我可以用两个来源。

这是我在UPSERT原始尝试：

IF ((SELECT COUNT(CAST(StudentuserID AS int)) FROM HL_StudentAttendance WHERE StudentUserID=1)>0)
UPDATE HL_StudentAttendance
SET
CID = CAST('[querystring:CID]' AS int),
CalendarEventID = CAST('[querystring:CEID]' AS int),
StudentUserID = CAST('[StudentUserID]' AS int),
Attendance = '[Attendance]'
ELSE
INSERT INTO HL_StudentAttendance
(CID,CalendarEventID,StudentUserID,Attendance)
VALUES
(CAST('[querystring:CID]' AS int), CAST('[querystring:CEID]' AS int), CAST('[StudentsUserID]' AS int),'[Attendance]')

尽管IF语句的结果是8所以8>0它应该运行我的更新它始终运行插入不知道的if / else逻辑丢失的东西我。

下面是一个尝试MERGE

MERGE 
   HL_StudentAttendance AS target
USING 
   HL_CourseRegistrations AS source
ON 
   target.StudentUserID = source.UserID
   AND source.
WHEN MATCHED THEN 
   UPDATE SET 
   Attendance = '[Attendance]'
WHEN NOT MATCHED THEN 
   INSERT (CID, CalendarEventID, StudentUserID, Attendance) VALUES ('[querystring:CID]', '[querystring:CEID]', '[UserID]', '[Attendance]')
;

在这里，我的问题是，我在我的表中的数据HL_CourseEvents应该用来抢的附加源项是CalendarEventID否则MERGE工作，但插入重复的项目进入我的HL_StudentAttendance因为不存在用于参数CalendarEventID

任何建议表示赞赏。

UPDATE

下面的查询不工作，只不过它插入新记录如果有什么在考勤领域的改变，而不是更新正确。我怀疑是因为我更新的WHERE子句中是不存在的。我尝试添加它得到执行错误

MERGE HL_StudentAttendance
USING
(
SELECT cr.CID, ce.CalendarEventID, cr.UserID FROM HL_CourseRegistrations cr
JOIN HL_CalendarEvents ce
ON ce.CID = cr.CID
) tmpTable
ON
HL_StudentAttendance.StudentUserID = tmpTable.UserID
AND HL_StudentAttendance.CalendarEventID = tmpTable.CalendarEventID
WHEN MATCHED THEN
UPDATE
SET
Attendance = 'Attended Late'
WHEN NOT MATCHED THEN 
INSERT (CID,CalendarEventID,StudentUserID,Attendance) VALUES ('1','1','1','555')
;

查询结果运行两次。如果我继续运行它，它总是插入更新的更多，而不是：

ID  CID CalendarEventID StudentUserID Attendance
1   1   1               1             Attended Late
2   1   1               1             Attended Late
3   1   1               1             Attended Late
4   1   1               1             Attended Late
5   1   1               1             Attended Late
6   1   1               1             555
7   1   1               1             555
8   1   1               1             555
9   1   1               1             555

基于更多的信息，下面试试这个。我砍死在一起的模式和一些数据仅用于演示目的，所以你对此怎么看你的模式进行比较。然而，在底部的两个例子，你在问什么，我相信。第一次插入一个新的记录，如果它不存在基于用户ID。第二个标记该记录为“参加晚”，如果它存在。需要注意的是，对于现实生活中，你可能会需要多个参数，用户可以为多个课程签订了，所以你要在课程或日历事件ID通过为好。让我知道，如果这让你开始或你需要额外的东西。

--
-------------------------------------------------
CREATE TABLE [test].[HL_StudentAttendance]
  (
       [StudentUserID]     INT
       , [CalendarEventID] INT
       , [Attendance]      NVARCHAR(250)
       , [CID]             INT
  );

CREATE TABLE [test].[HL_CourseRegistrations]
  (
       [CID]      INT
       , [UserID] INT
  );

CREATE TABLE [test].[HL_CalendarEvents]
  (
       [CalendarEventID] INT
       , [CID]           INT
  );

go

--
-------------------------------------------------
INSERT INTO [test].[HL_CourseRegistrations]
            ([CID]
             , [UserId])
VALUES      (1,1),
            (3,4),
            (4,5);

INSERT INTO [test].[HL_CalendarEvents]
            ([CalendarEventID]
             , [CID])
VALUES      (1,1);

go

--
-------------------------------------------------
CREATE PROCEDURE [test].[set_attendance] @user INT
AS
    BEGIN
        MERGE INTO [test].[HL_StudentAttendance] AS [target]
        USING (SELECT [cr].[CID]
                      , [ce].[CalendarEventID]
                      , [cr].[UserID]
               FROM   [test].[HL_CourseRegistrations] [cr]
                      JOIN [test].[HL_CalendarEvents] [ce]
                        ON [ce].[CID] = [cr].[CID]) AS [source]
        ON [target].[StudentUserID] = @user
           AND [target].[CalendarEventID] = [source].[CalendarEventID]
        WHEN MATCHED THEN
            UPDATE SET [Attendance] = 'Attended Late'
        WHEN NOT MATCHED BY target THEN
            INSERT ([CID]
                    , [CalendarEventID]
                    , [StudentUserID]
                    , [Attendance])
            VALUES ('1'
                    , '1'
                    , @user
                    , '555');
    END;

go

--
-- inserts a new record
-------------------------------------------------
EXECUTE [test].[set_attendance]
    @user=12;

SELECT *
FROM   [test].[HL_StudentAttendance];


--
-- marks an existing record as late
-------------------------------------------------
EXECUTE [test].[set_attendance]
    @user=12;

SELECT *
FROM   [test].[HL_StudentAttendance];