在行与SQL Server匹配或不匹配时插入或更新数据

Question

我有表ProjectTemplateSection:

[ProjectID] [int] 
[ProjectTemplateID]
[SectionID] [int]
[IsActive] [bit] 
[SectionOrderNumber] [int] 

样本数据如下：

ProjectID  TemplateCloneId    SectionCloneId IsActive SectionOrderNumber
-----------------------------------------------------------------------
1                   1               1         1         4
1                   1               2         0         2
1                   1               3         1         3

我们有项目，模板&相互关联的部分模块

• 一个模板将有多个部分
• 一个项目将映射一个模板

当用户将模板分配给项目时，项目ID、templateid&sectionid将插入ProjectTemplateSection中。

我需要关于以下澄清的建议

• 如果用户来了&编辑模板的节，如删除节&添加新的部分。如果在区段&模板，则IsActive标志应为false。如果没有映射，则应该插入

我应该为所选模板的现有映射执行选择查询吗&获取记录&与新的映射&为新记录插入&更新旧映射，即使IsActive标志为假

请建议哪种是这种情况下的最佳解决方案

Answer 1

尝试使用MERGE:

CREATE TABLE TestProjectTemplateSection(
ProjectID int,
ProjectTemplateID int,
SectionID int,
IsActive bit,
SectionOrderNumber int
)
INSERT TestProjectTemplateSection(ProjectID,ProjectTemplateID,SectionID,IsActive,SectionOrderNumber)VALUES
(1,1,1,1,4), 
(1,1,2,0,2),
(1,1,3,1,3)

以及插入/更新/停用程序：

CREATE PROC SetTestProjectTemplateSection
@ProjectID int,
@ProjectTemplateID int,
@SectionID int,
@IsActive int=1,
@SectionOrderNumber int=NULL
AS
MERGE TestProjectTemplateSection trg
USING
(
SELECT
@ProjectID ProjectID,
@ProjectTemplateID ProjectTemplateID,
@SectionID SectionID,
@IsActive IsActive,
@SectionOrderNumber SectionOrderNumber
) src
ON trg.ProjectID=src.ProjectID AND trg.ProjectTemplateID=src.ProjectTemplateID AND trg.SectionID=src.SectionID
WHEN MATCHED THEN -- update IsActive and SectionOrderNumber
UPDATE SET
trg.IsActive=src.IsActive,
trg.SectionOrderNumber=ISNULL(src.SectionOrderNumber,trg.SectionOrderNumber) -- if NULL then not change it
WHEN NOT MATCHED BY TARGET THEN -- insert new row
INSERT(ProjectID,ProjectTemplateID,SectionID,IsActive,SectionOrderNumber)
VALUES(src.ProjectID,src.ProjectTemplateID,src.SectionID,src.IsActive,src.SectionOrderNumber);
GO

演示：

-- case 1 - update
EXEC SetTestProjectTemplateSection 1,1,2,1
EXEC SetTestProjectTemplateSection 1,1,2,1,111 -- set new order number
SELECT *
FROM TestProjectTemplateSection
-- case 2 - insert
EXEC SetTestProjectTemplateSection 2,1,2,1,123
SELECT *
FROM TestProjectTemplateSection
-- case 3 - deactive
EXEC SetTestProjectTemplateSection 2,1,2,0
SELECT *
FROM TestProjectTemplateSection

我认为作为变体，您可以使用STRING_SPLIT作为部分列表(参见@ListOfSectionID(：

CREATE PROC SetTestProjectTemplateSections
@ProjectID int,
@ProjectTemplateID int,
@ListOfSectionID varchar(100), -- e.g. '1,2,3,4'
@IsActive int=1
AS
MERGE TestProjectTemplateSection trg
USING
(
SELECT
@ProjectID ProjectID,
@ProjectTemplateID ProjectTemplateID,
CAST(value AS int) SectionID,
@IsActive IsActive
FROM STRING_SPLIT(@ListOfSectionID,',')
) src
ON trg.ProjectID=src.ProjectID AND trg.ProjectTemplateID=src.ProjectTemplateID AND trg.SectionID=src.SectionID
WHEN MATCHED THEN
UPDATE SET
trg.IsActive=src.IsActive
WHEN NOT MATCHED BY TARGET THEN
INSERT(ProjectID,ProjectTemplateID,SectionID,IsActive)
VALUES(src.ProjectID,src.ProjectTemplateID,src.SectionID,src.IsActive);
GO

但STRING_SPLIT适用于SQL Server 2017。对于SQL Server的另一个版本，您可以找到用于拆分的用户定义函数。例如T-SQL拆分字符串

测试：

EXEC SetTestProjectTemplateSections 1,1,'1,2,3,4',0
SELECT *
FROM TestProjectTemplateSection

---

作为变量，您可以使用表格参数：

CREATE TYPE TypeForProjectTemplateSection AS table(
ProjectID int,
ProjectTemplateID int,
SectionID int,
IsActive bit,
SectionOrderNumber int
)
GO

在你的程序中使用这种类型：

CREATE PROC SetTestProjectTemplateSectionFromTable
@ProjectTemplateSectionDate dbo.TypeForProjectTemplateSection READONLY -- need to use READONLY here
AS
MERGE TestProjectTemplateSection trg
USING @ProjectTemplateSectionDate src
ON trg.ProjectID=src.ProjectID AND trg.ProjectTemplateID=src.ProjectTemplateID AND trg.SectionID=src.SectionID
WHEN MATCHED THEN -- update IsActive and SectionOrderNumber
UPDATE SET
trg.IsActive=src.IsActive,
trg.SectionOrderNumber=src.SectionOrderNumber
WHEN NOT MATCHED BY TARGET THEN -- insert new row
INSERT(ProjectID,ProjectTemplateID,SectionID,IsActive,SectionOrderNumber)
VALUES(src.ProjectID,src.ProjectTemplateID,src.SectionID,src.IsActive,src.SectionOrderNumber);
GO

如何使用：

声明@表dbo。项目模板部分的类型

INSERT @Table(ProjectID,ProjectTemplateID,SectionID,IsActive,SectionOrderNumber)VALUES
(1,1,1,1,111), 
(1,1,2,1,222)
EXEC SetTestProjectTemplateSectionFromTable @Table -- use it as parameter
SELECT *
FROM TestProjectTemplateSection

你可以从C#使用它：

SqlCommand cmd = new SqlCommand("SetTestProjectTemplateSectionFromTable", con);
cmd.CommandType = CommandType.StoredProcedure;
DataTable tbl = new DataTable();
tbl.Columns.Add("ProjectID", typeof(int));
tbl.Columns.Add("ProjectTemplateID", typeof(int));
tbl.Columns.Add("SectionID", typeof(int));
tbl.Columns.Add("IsActive", typeof(bool)); // Check it because I don't remember how to use type bit here
tbl.Columns.Add("SectionOrderNumber", typeof(int));
tbl.Rows.Add(1,1,1,true,111);
tbl.Rows.Add(1,1,2,true,222);
cmd.Parameters.AddWithValue("@ProjectTemplateSectionDate", tbl);
cmd.ExecuteNonQuery();