使用用户定义的表类型在 SQL 中插入和更新

2021-09-10 00:00:00 tsql sql-server

以下是我创建的新数据类型.

Following is new data type that I created.

CREATE TYPE [dbo].[UpdateHotelTableType] AS TABLE(
    [ID] [int] NULL,
    [HotelID] [int] NULL,
    [FromDate] [datetime] NULL,

)

以下是我使用上述数据类型的存储过程.

Following is my stored procedure that I used the above datatype.

ALTER PROCEDURE [dbo].[SP_Hotel_Info_Update]
     -- Add the parameters for the stored procedure here
    @XHotelInfoDetails UpdateHotelTableType READONLY,

AS
BEGIN

    Update dbo.HotelInfo
    SET 
    FromDate = r.FromDate,
    from @XHotelInfoDetails r
    Where HotelInfo.ID = r.ID

END

这适用于数据库中的更新结果.但我想检查 id 是否存在，如果 id 不存在，则将该行插入表中.否则更新当前记录.在这里，我正在发送要更新的数据列表.

This is working fine for update results in database. But I want to check whether the id is exists and if the id is not exists insert the row in to the table. otherwise update current record. In here I am sending the list of data for update.

谁能帮我重新创建存储过程，通过检查ID是否存在来插入数据.

Can any one help me to recreate the stored procedure to insert the data too by checking the existence of ID.

推荐答案

使用 MERGE:

根据与源表的联接结果对目标表执行插入、更新或删除操作.例如，您可以通过插入、更新或删除行来同步两个表在一个表中基于在另一表中发现的差异.

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. 

ALTER PROCEDURE [dbo].[SP_Hotel_Info_Update]
     -- Add the parameters for the stored procedure here
    @XHotelInfoDetails UpdateHotelTableType READONLY,

AS
BEGIN

    MERGE dbo.HotelInfo AS trg
    USING @XHotelInfoDetails AS src
      ON src.ID = trg.ID
     WHEN MATCHED THEN
       UPDATE SET FromDate = src.FromDate
     WHEN NOT MATCHED BY TARGET THEN
       INSERT (col1, col2, ...)
       VALUES (src.col1, src.col2, ...);
END

在我的数据表中，可以有新添加的行以及已删除的行.那么如何比较 id 并从 hotelinfo 表中删除行?

In my datatable, there can be newly added rows as well as deleted rows. So how can I compare the id and delete rows from hotelinfo table?

您可以添加新子句:

WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
     THEN DELETE;

在特定条件下从目标中删除数据.

with specific condition to delete data from target.

相关文章