Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

Or:

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?

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

UPDATE then INSERT usually. Simply, it's less work.

In this case, you have an ID columns (IDENTITY): I'll assume this is the clustered index

You delete rows, you leave gaps in pages = fragmentation. You add rows, you probably need more pages allocated. Other processes are doing this too.

An UPDATE will update in-situ and you'll have a less expensive INSERT because there is less rows.

Saying that...

If your new:update is 100:1 then it doesn't really matter of course. And the EXISTS is required too.

However, from a raw "shifting data" perspective UPDATE..INSERT would be my choice

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.