如果存在，则使用 VB.net 更新其他插入(sql 参数化查询) [英] If Exists Update Else Insert with VB.net (sql parameterised query)

问题描述

我希望使用以下更新\插入方法来加速我的应用程序插入\更新:

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')

我将如何使用 sql 参数执行此操作?由于@@，rowcount 函数将作为参数被拾取.

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

推荐答案

这种模式被称为 UPSERT.这样做的现代方法是在 SQL Server 2008 中使用 MERGE例如

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);

这篇关于如果存在，则使用 VB.net 更新其他插入(sql 参数化查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！