我一直在尝试编写一个存储过程,使用Merge执行UpSert,并满足以下条件:
如果记录已存在,则将目标的EndDate更改为昨天,即当前日期-1
如果记录不存在,则插入新记录
这是我在SP中使用的tblEmployee表:
CREATE TABLE tblEmployee
(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](10) NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL
)
这是我的存储过程,它以UDTT作为输入参数。
CREATE PROCEDURE [dbo].[usp_UpsertEmployees]
@typeEmployee typeEmployee READONLY -- It has same column like tblEmployye except EmployeeID
AS
BEGIN
SET NOCOUNT ON;
MERGE INTO tblEmployee AS TARGET
USING @typeEmployee AS SOURCE
ON TARGET.Name = SOURCE.Name
WHEN MATCHED and TARGET.StartDate < SOURCE.StartDate
THEN
--First Update Existing Record EndDate to Previous Date as shown below
UPDATE
set TARGET.EndDate = DATEADD(day, -1, convert(date, SOURCE.StartDate))
-- Now Insert New Record
--INSERT VALUES(SOURCE.Name, SOURCE.StartDate, SOURCE.EndDate);
WHEN NOT MATCHED by TARGET
THEN
INSERT VALUES(SOURCE.Name, SOURCE.StartDate, SOURCE.EndDate);
SET NOCOUNT OFF;
END
如何在匹配某一列时同时进行现有记录的更新和新记录的添加
请问有人可以向我解释 TSQL 中 Merge 的执行流程吗?
WHEN MATCHED --Will this Execute Everytime
WHEN NOT MATCHED by TARGET -- Will this Execute Everytime
WHEN NOT MATCHED by SOURCE -- Will this Execute Everytime
以上三个条件在合并过程中每次都会执行,还是只有匹配条件会每次执行?感谢提前帮助。