Home TSQL MERGE with multiple sources
Reply: 0

TSQL MERGE with multiple sources

user3181
1#
user3181 Published in July 22, 2018, 6:19 pm

Originally i tried using an IF/ELSE to accomplish an "UPSERT" and someone suggested I use MERGE my issue with MERGE is it does not look like I can use two sources.

Here is my Original attempt at an 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]')

Even though the IF statement result is 8 so 8>0 it should run my update it's always running an insert not sure if my if/else logic is missing something.

Here is an attempt with 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]')
; 

My issue here is that I have data in my table HL_CourseEvents that should be used to grab an additional source item being CalendarEventID otherwise the MERGE works but inserts duplicate entries into my HL_StudentAttendance as there is no parameter for CalendarEventID

Any suggestions appreciated.

UPDATE

The following query does work, except that it's inserting new records if anything is changed on the attendance field rather than update properly. I suspect because the WHERE clause on my update is not there. I tried adding it getting execution errors

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

Results of the query run twice. If i keep running it, it will always insert more instead of updating:

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.313985 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO