I know I don’t often talk about code, but I have recently had the good fortune to get back into playing around with the new version of SQL Server 2005. I think I’m going to do a few small posts on some of the new things I’ve found that I like.
Insert and Merge, finally your can ‘Upsert’!
Upsert is a mythical feature where you can insert and update data with one command. It's not quite in one command but the effect is the same with a simple set of syntax.
MERGE INTO DavesTable
USING DavesTempTable
ON DavesTable.MatchingField1 = DavesTempTable.MatchingField1
WHEN MATCHED THEN
UPDATED UpdatedField1 = DavesTempTable.UpdatedField1
WHEN NOT MATCH THEN
INSERT VALUES(DavesTempTable.MatchingField1, DavesTempTable.UpdateField1)
Although it’s nice to finally have this feature and the syntax is simple and elegant I’m wondering if many people will actual use it in anger.
UPDATE: August 2008: Well the MERGE command didn't finally make it until SQL Server 2008. It didn't make 2005 and was pulled in the RTM version.
Now the TOP command has always been a great feature but what has always been a right pain is that it has to be a constant in SQL Server 2000 which has really limited it’s use.
Now this syntax is valid!
-- show the first x products names in alphabetical order.
DECLARE @x bigint
SET @x = 25
SELECT TOP (@x) ProductID, Name
FROM Product
ORDER BY NAME
Just like the previous version of the TOP command it was well used in scalar statements. With the ability to pass the value to the statement from the outside I can see this going to be a very powerful new feature used for reporting especially as the PERCENT suffix is still valid. The only departure from the previous version is that you will need to use brackets if the value is a variable, use a constant and you won’t need them.