如何在 SQL Server 2005 中更新插入(更新或插入) [英] How to upsert (update or insert) in SQL Server 2005

问题描述

我有一个表，我在其中为员工插入行，但是下次当我想插入行时，我不想再次为该员工插入数据，只想更新所需的列，如果它在那里退出，则创建新行

I have table in which I am inserting rows for employee but next time when I want to insert row I don't want to insert again data for that employee just want to update with required columns if it exits there if not then create new row

我们如何在 SQL Server 2005 中做到这一点?

How can we do this in SQL Server 2005?

我使用的是jsp

我的查询是

String sql="insert into table1(id,name,itemname,itemcatName,itemQty)values('val1','val2','val3','val4','val5')";

如果是第一次，则将其插入数据库，否则如果存在则更新它

if it's first time then insert it into database else if exists update it

怎么办?

推荐答案

尝试检查是否存在:

IF NOT EXISTS (SELECT * FROM dbo.Employee WHERE ID = @SomeID)

    INSERT INTO dbo.Employee(Col1, ..., ColN)
    VALUES(Val1, .., ValN)

ELSE

    UPDATE dbo.Employee
    SET Col1 = Val1, Col2 = Val2, ...., ColN = ValN
    WHERE ID = @SomeID

您可以轻松地将其包装到一个存储过程中，然后从外部调用该存储过程(例如，从 C# 之类的编程语言或您使用的任何语言中调用).

You could easily wrap this into a stored procedure and just call that stored procedure from the outside (e.g. from a programming language like C# or whatever you're using).

更新:或者您可以将整个语句写在一个长字符串中(可行 - 但不是很有用) - 或者您可以将其包装到一个存储过程中:

Update: either you can just write this entire statement in one long string (doable - but not really very useful) - or you can wrap it into a stored procedure:

CREATE PROCEDURE dbo.InsertOrUpdateEmployee
       @ID INT,
       @Name VARCHAR(50),
       @ItemName VARCHAR(50),  
       @ItemCatName VARCHAR(50),
       @ItemQty DECIMAL(15,2)
AS BEGIN
    IF NOT EXISTS (SELECT * FROM dbo.Table1 WHERE ID = @ID)
       INSERT INTO dbo.Table1(ID, Name, ItemName, ItemCatName, ItemQty)
       VALUES(@ID, @Name, @ItemName, @ItemCatName, @ItemQty)
    ELSE
       UPDATE dbo.Table1
       SET Name = @Name,
           ItemName = @ItemName,
           ItemCatName = @ItemCatName,
           ItemQty = @ItemQty
       WHERE ID = @ID
END

然后从您的 ADO.NET 代码中调用该存储过程

and then just call that stored procedure from your ADO.NET code

这篇关于如何在 SQL Server 2005 中更新插入(更新或插入)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！