如何在 SQLServer2005中插入(更新或插入)

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

我们如何在 SQLServer2005中做到这一点？

我正在使用 jsp

我的疑问是

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

如果它是第一次，然后插入到数据库中，如果存在更新它

怎么办？

159556 次浏览
小开
最佳答案

试着检查是否存在:

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 # 之类的编程语言或您正在使用的任何语言)。

更新: 或者你可以只用一个长字符串来写这个完整的语句(可行，但是不是很有用) ，或者你可以把它封装到一个存储过程中:

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 代码中调用该存储过程

小开

您可以使用表上的 INSTEAD OF INSERT触发器执行此操作，该触发器检查行是否存在，然后根据行是否已经存在更新/插入。在 MSDN给你上有一个如何为 SQLServer2000 + 执行此操作的示例:

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
-- Log attempt to insert duplicate Person row in PersonDuplicates table.
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
-- Check for duplicate Employee. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
--If duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END
小开

您可以使用 @@ROWCOUNT检查是否应该插入或更新行:

update table1
set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
where id = 'val1'
if @@ROWCOUNT = 0
insert into table1(id, name, itemname, itemcatName, itemQty)
values('val1', 'val2', 'val3', 'val4', 'val5')

在这种情况下，如果更新失败，将插入新行

小开

您可以检查该行是否存在，然后检查 INSERT 或 UPDATE，但这保证您将执行两个 SQL 操作，而不是一个:

  1. 检查行是否存在
  2. 插入或更新行

更好的解决方案是始终先更新 UPDATE，如果没有更新行，那么执行 INSERT，如下所示:

update table1
set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
where id = 'val1'


if @@ROWCOUNT = 0
insert into table1(id, name, itemname, itemcatName, itemQty)
values('val1', 'val2', 'val3', 'val4', 'val5')

这将采用一个 SQL 操作或两个 SQL 操作，具体取决于行是否已经存在。

但是，如果性能确实是一个问题，那么您需要弄清楚这些操作更可能是 INSERT 操作还是 UPDATE 操作。如果 UPDATE 更常见，请执行上述操作。如果 INSERT 更常见，则可以反过来执行，但必须添加错误处理。

BEGIN TRY
insert into table1(id, name, itemname, itemcatName, itemQty)
values('val1', 'val2', 'val3', 'val4', 'val5')
END TRY
BEGIN CATCH
update table1
set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
where id = 'val1'
END CATCH

为了确定是否需要执行 UPDATE 或 INSERT，必须在单个 TRANSAION 中执行两个操作。理论上，在第一个 UPDATE 或 INSERT (甚至 EXISTS 检查)之后，但是在下一个 INSERT/UPDATE 语句之前，数据库可能已经更改，导致第二个语句无论如何都会失败。这种情况非常罕见，交易的开销可能不值得这样做。

或者，您可以使用一个称为 MERGE 的 SQL 操作来执行 INSERT 或 UPDATE，但是对于这个单行操作来说，这可能有点过了。

考虑阅读关于 SQL 事务语句，比赛条件，SQL MERGE 语句的文章。

小开

下面是 Michael J. Swart 关于这个问题的一篇有用的文章，其中介绍了在 SQL Server 中实现 UPSERT的不同模式和反模式:
Https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/

它解决了相关的并发问题(主键冲突、死锁)——这里提供的所有答案在本文中都被认为是反模式(使用触发器的@Bridge 解决方案除外，这里没有介绍)。

以下是文章摘录，作者倾向于这种解决办法:

在具有锁提示的可序列化事务中:

CREATE PROCEDURE s_AccountDetails_Upsert ( @Email nvarchar(4000), @Etc nvarchar(max) )
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN


IF EXISTS ( SELECT * FROM dbo.AccountDetails WITH (UPDLOCK) WHERE Email = @Email )


UPDATE dbo.AccountDetails
SET Etc = @Etc
WHERE Email = @Email;


ELSE


INSERT dbo.AccountDetails ( Email, Etc )
VALUES ( @Email, @Etc );


COMMIT

这里还有一个关于堆栈溢出的相关问题: 在 SQLServer 上插入更新存储进程


提交答案