How do these SQL-Server-2005 MERGE alternatives compare

In SQL-Server-2005 how are the following approaches likely to compare in terms of performance for a bulk update of dat开发者_StackOverflow社区a.

Bulk Delete then Bulk Insert

--Delete Existing Data
DELETE FROM Foo
WHERE Category = 1;

--Insert New Data
INSERT INTO Foo (Id, Category, ...)
SELECT Id, Category, ...
FROM @Temp;

(Merge Style) Bulk Insert/Update/Delete

--Delete Existing Data Not In New Data
DELETE FROM Foo 
FROM Foo LEFT OUTER JOIN @Temp T ON Foo.Id = T.Id
WHERE Foo.Category = 1
AND T.Id IS NULL;

--Update Matching Data
UPDATE Foo
SET
    Foo.Category = T.Category,
...
FROM Foo INNER JOIN @Temp T ON Foo.Id = T.Id;

--Insert New Data
INSERT INTO Foo (Id, Category, ...)
SELECT Id, Category, ...
FROM @Temp T LEFT OUTER JOIN Foo F ON F.Id = T.Id
WHERE F.Id IS NULL;

Unless you are pressed for time (lazy) and you know for a fact that the second form is not required, the second form is always preferred in a production, busy system.

You should beef up the 2nd one so that identity updates (update resulting in no change) is not carried out. Currently, your code will update a record to itself even if it is exactly the same before and after - in this case, it is almost the same as the first form.

Some differences:

• In the proper 2nd form, existing records with attached references (foreign key hooks) are not broken, whereas the first could break since it attempts to momentarily remove all records.
• The proper 2nd form has less to log
• The proper 2nd form will have less to do in triggers (if the table had triggers)

This will depend entirely on your current data structure. What are the fields? How many? What are the indexes? How big are the tables you are inserting into/updating/selecting from?

Your first alternative has less logic involved, and could be faster, but it will depend on what field(s) you are updating, how many there are, what the values and datatypes are, what indexes, etc.

Short answer - it depends.

In SQL there is (normally) no "best" answer. There is a best solution for a given situation but if you don't share the details you won't be able to get a good answer.

Also, BULK INSERT is a special kind of function normally using BCP. You can do a minimally logged insert but what you have listed is not a BULK INSERT.