Replacing Merge in SQL Server with some other code

Question

How can I replace the merge query with some other code in SQL Server 2005 in below code to get the same functionality.

 alter trigger [Emp_Update_Logging] on [Employee_Test]
  after update
  as


   MERGE INTO dbo.Emp_Log EL
   USING INSERTED I
      ON EL.EID = I.Emp_ID
   WHEN MATCHED THEN
   UPDATE 
      SET EL.ModifiedDate = getdate()
   WHEN NOT MATCHED THEN 
      INSERT(EID,ModifiedDate)
      VALUES(I.Emp_ID,getdate());

  go

Answer 1

You basically need to break up the MERGE into two operations - an INSERT for those rows that don't exist yet, and an UPDATE for those rows that do exist already.

Something like this might work:

ALTER TRIGGER [Emp_Update_Logging] ON [Employee_Test]
AFTER UPDATE 
AS
    -- insert those rows from Inserted that don't exist yet
    INSERT INTO dbo.Emp_Log(EID, ModifiedDate)
      SELECT I.Emp_ID, GETDATE()
      FROM Inserted i
      WHERE NOT EXISTS (SELECT * FROM dbo.Emp_Log WHERE EID = i.Emp_ID)

    -- update those rows that already exist
    UPDATE dbo.Emp_Log
    SET ModifiedDate = GETDATE()
    FROM Inserted i
    WHERE EXIST (SELECT * FROM dbo.Emp_Log WHERE EID = i.Emp_ID)

However, since this is an UPDATE trigger, I would argue that you will never have rows that don't exist yet - after all, this trigger only fires when an EXISTING row is updated .....

Answer 2

I think you want to do the UPDATE first, then do the INSERT.

For a general generic solution for replacing Merge with some other SQL code, Marc's answer does not look 100% correct.

In his specific answer, there's no harm, because for both an insert and an update, the example is just updating a timestamp to now. HOWEVER, we can not use this format if different things are done with the data based on whether it's an Insert or an Update. In this answer, the Insert adds the missing rows... but the Update will update ALL rows, not just the old rows that previously existed.

The newly-inserted rows in step 1 are now a part of the Where Exists clause in step 2.

The simple solution to the issue I just raised in this answer would be to do the UPDATE first, then do the INSERT.

ALTER TRIGGER [Emp_Update_Logging] ON [Employee_Test]
AFTER UPDATE 
AS

    -- update those rows that already exist
    UPDATE dbo.Emp_Log
    SET ModifiedDate = GETDATE()
    FROM Inserted i
    WHERE EXIST (SELECT * FROM dbo.Emp_Log WHERE EID = i.Emp_ID)

    -- insert those rows from Inserted that don't exist yet
    INSERT INTO dbo.Emp_Log(EID, ModifiedDate)
      SELECT I.Emp_ID, GETDATE()
      FROM Inserted i
      WHERE NOT EXISTS (SELECT * FROM dbo.Emp_Log WHERE EID = i.Emp_ID)

Also... I'm not a big fan of the Where EXIST / NOT EXIST subclauses. In some cases it would be beneficial to left join with dbo.Emp_Log and add to your where clause to check for a value to be NULL or not.