If Exists Update Else Insert with VB.net (sql parameterised query)

Question

I'm hoping to use the following update\insert method to speed up my application insert\updates:

UPDATE [Application_Type_Table] 
SET ApplicationType='Test Value'
WHERE ID='1'
IF @@ROWCOUNT=0
INSERT INTO [Application_Type_Table] VALUES ('Test Value')

How would I do this with sql parameters? the rowcount function will be picked up as a parameter due to the @@.

sqlCommand.CommandText = _
"UPDATE [Application_Type_Table]" _ 
SET (SET ApplicationType=@TestValue" _ 
"WHERE ID=@RecordID IF @@ROWCOUNT=0 INSERT INTO [Application_Type_Table] VALUES (@TestValue)"

http://blogs.msdn.com/b/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx

Answer 1

I would suggest moving the query to a stored procedure. Queries (especially multi-statement ones) don't really belong hard-coded into one's application IMO. Doing so would also have the bonus of alleviating your problem ;)

Answer 2

sqlCommand.CommandText = "IF NOT EXISTS (SELECT 1 FROM  [Application_Type_Table] WHERE "_
"ID='1') BEGIN INSERT INTO " _
"[Application_Type_Table] VALUES ('Test Value') END ELSE BEGIN UPDATE [Application_Type_Table]"_
"SET ApplicationType='Test Value'"_
"WHERE ID='1' END"

Answer 3

This pattern is known as an UPSERT. The modern way of doing this is to use MERGE in SQL Server 2008 e.g.

MERGE INTO Application_Type_Table AS target 
USING (
       VALUES (@RecordID, @TestValue)
      )  AS source (ID, ApplicationType)
   ON target.ID = source.ID
WHEN MATCHED 
   THEN UPDATE 
           SET ApplicationType = source.ApplicationType
WHEN NOT MATCHED 
   THEN INSERT (ID, ApplicationType) 
           VALUES (ID, ApplicationType);