时间戳

首页 > 解决方案 > 不一致的列和合并操作

sql - 不一致的列和合并操作

问题描述

我有一个带有列的表：

CREATE TABLE Tip
(
    ID int CONSTRAINT PK_Tip_ID PRIMARY KEY IDENTITY,
    Title varchar(100) NOT NULL,
    Description varchar(255) NOT NULL,
    Status varchar(10) NOT NULL,
    Created date DEFAULT GETDATE(),
    Expiration date,
    Published date NOT NULL,
    Link varchar(255),
    Category varchar(50) CONSTRAINT FK_Category FOREIGN KEY REFERENCES TipCategory(Name),
    Photo int CONSTRAINT FK_Photo FOREIGN KEY REFERENCES TipPhoto(ID),
    Highlighted bit CONSTRAINT DF_Highlighted DEFAULT 0
);

我想创建过程并使用 MERGE 操作在单个语句中对表执行 INSERT 和 UPDATE 操作，但我有一个问题。如果所有列（当然除了 ID）都不一致怎么办？我的意思是，如果我想从前端更改所有单列并将它们发送到此过程怎么办？如果我没有至少一个组成列，那么如何找到如下匹配项：

MERGE Production.UnitMeasure AS target  
USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)  
ON (target.UnitMeasureCode = source.UnitMeasureCode)  
WHEN MATCHED THEN
    UPDATE SET Name = source.Name  
WHEN NOT MATCHED THEN  
    INSERT (UnitMeasureCode, Name)  
    VALUES (source.UnitMeasureCode, source.Name)

我没有任何常量列来决定是否应该插入或更新这些值。

标签： sqlsql-servertsqlstored-procedures

解决方案

我决定插入或更新取决于 ID，如下所示：

CREATE PROCEDURE createEditTip
@ID int = NULL,
@Title varchar(100),
@Description varchar(255),
@Status varchar(10),
@Expiration date = NULL,
@Published date,
@Link varchar(255),
@Category varchar(50),
@PhotoID int,
@Highlighted bit

AS
BEGIN

    MERGE Tip AS target  
    USING (SELECT @ID, @Title, @Description, @Status, @Expiration, @Published, @Link, @Category, @PhotoID, @Highlighted) 
    AS source (ID, Title, Description, Status, Expiration, Published, Link, Category, PhotoID, Highlighted)  
    ON (target.ID = source.ID)  
    WHEN MATCHED THEN
        UPDATE SET Title = source.Title,
                   Description = source.Description,
                   Status = source.Status,
                   Expiration = source.Expiration,
                   Published = source.Published,
                   Link = source.Link,
                   Category = source.Category,
                   Photo = source.PhotoID,
                   Highlighted = source.Highlighted
    WHEN NOT MATCHED THEN  
        INSERT (Title, Description, Status, Expiration, Published, Link, Category, Photo, Highlighted)  
        VALUES (source.Title, source.Description, source.Status, source.Expiration, source.Published, source.Link, source.Category, source.PhotoID, source.Highlighted);

    IF NOT EXISTS (select 1 from TipCategory where Name = @Category)
    BEGIN
        insert into TipCategory
        values (@Category);
    END
END

我认为这是安静的好解决方案。

推荐阅读