I'm using SQL server 2005. I have a table that is basically a list of ids with timestamps that is recording the last time an event happened for that id.
I have a list of ids in a table variable, and I want to update the table with the current time for all the ids that are already in the table, and insert new records for the ids that are not yet in the table. SQL 2005 doesn't have a MERGE statement or a ON DUPLICATE KEY UPDATE.
Which of these forms is preferred:
UPDATE IdTimes SET LastEventTime = GetDate() WHERE Id in (SELECT NewId FROM @NewIds) INSERT INTO IdTimes(Id, LastEventTime) SELECT NewId, GetDate() FROM @NewIds WHERE NOT EXISTS (SELECT Id FROM IdTimes WHERE NewId = Id)
DELETE FROM IdTimes WHERE Id IN (SELECT NewId FROM @NewIds) INSERT INTO IdTimes(Id, LastEventTime) SELECT NewId, GetDate() FROM @NewIds
Or is there a better solution to achieve this?