tsql - T-Sql修改插入SProc以更新(如果存在)

Question

情境
我有一个用T-Sql编写的存储过程，用于将数据作为XML插入表中。
由于数据会定期更新，因此我希望对行进行更新(如果行已存在)(除了首次运行应用程序时，它们将始终存在)。
题
下面是我的Insert Sproc的代码，但是我似乎无法锻炼存储过程的Update端并且希望获得一些帮助。
码

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[INS_Curve] 
(
 @Curve varchar(MAX)
)
AS
DECLARE @handle int
 exec sp_xml_preparedocument @handle OUTPUT, @Curve

 INSERT INTO CurveDB..tblCurve(LoadID,BusinessDate, Factor)
  SELECT LoadID,BusinessDate, Factor
  FROM OPENXML(@handle, 'NewDataSet/Table1',2)
  WITH(
  LoadID int,
  BusinessDate DateTime,
  Factor float
  ) 

   exec sp_xml_removedocument @handle

Answer 1

与其直接插入目标表，不如将XML文档中的数据放入临时表，然后从那里插入/更新目标...

DECLARE @tabTemporary TABLE
(
  LoadID INT,
  BusinessDate DATETIME,
  Factor FLOAT
)

exec sp_xml_preparedocument @handle OUTPUT, @Curve

 INSERT INTO @tabTemporary (LoadID,BusinessDate, Factor)
  SELECT LoadID,BusinessDate, Factor
  FROM OPENXML(@handle, 'NewDataSet/Table1',2)
  WITH(
  LoadID int,
  BusinessDate DateTime,
  Factor float
  )

exec sp_xml_removedocument @handle

 INSERT INTO CurveDB..tblCurve
 ( LoadID, BusinessDate, Factor )
 SELECT LoadID, BusinessDate, Factor
 FROM @tabTemporary T1
 WHERE NOT EXISTS
 (
 SELECT 1 FROM CurveDB..tblCurve T2 WHERE T1.LoadID = T2.LoadID
 )

 UPDATE T1
 SET T1.BusinessDate = T2.BusinessDate, T1.Factor = T2.Factor
 FROM CurveDB..tblCurve T1
 INNER
 JOIN @tabTemporary T2
 ON T1.LoadID = T2.LoadID

注意:我假设“LoadID”是唯一/主键

更新

在Sql Server 2008中还引入了 "UPSERT" functionality，可能对此很感兴趣。