Downsides to MERGE with dummy USING table?
I'm creating some stored procedures specifically for use from C# to update various tables in our database. A large number of items require a predictable function that will:
1) Check if a matching row already exists 2) If it doesn't exist, insert data 3) Gather ID of row and return to user
I know this can be done in a number of ways, but the most elegant way I can imagine seems to be using a MERGE with a dummy table and using the procedure params for the ON clause, such as:
CREATE PROCEDURE dbo.UpdatePerson(@PersonID INT, @FirstName VARCHAR(50)) AS MERGE dbo.Person p USING (SELECT 1 One) One ON p.Person_ID = @PersonID WHEN MATCHED THEN UPDATE SET First_Name = @FirstName WHEN NOT MATCHED THEN INSERT (Person_ID, First_Name) VALUES (@PersonID, @FirstName);
This wraps it all together in one nice bundle, even though I'm not working with an actual table to merge in. I know the same basic idea could be accomplished with:
... USING (SELECT @PersonID Person_ID, @FirstName First_Name) NewPerson ON p.Person_ID = NewPerson.Person_ID ...
and maybe this would offer some kind of performance benefit?
Can anyone offer any solid reasons for/against this kind of usage of MERGE?
Instead of using MERGE you can use if condition.
You are having a temp table
CREATE TABLE #Table(PersonID INT,First_Name VARCHAR(100)) -- BEFORE THAT INSERT INTO TEMP TABLE IF EXISTS(SELECT 1 FROM YOURTABLE WHERE PERSONID IN(SELECT PERSONID FROM #TABLE)) BEGIN -------YOUR UPDATE QUERY END ELSE BEGIN -------INSERT QUERY END DROP TABLE #Table