問題描述

以下是我創(chuàng)建的新數(shù)據(jù)類型.

Following is new data type that I created.

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

)

以下是我使用上述數(shù)據(jù)類型的存儲過程.

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

這適用于數(shù)據(jù)庫中的更新結(jié)果.但我想檢查 id 是否存在，如果 id 不存在，則將該行插入表中.否則更新當(dāng)前記錄.在這里，我正在發(fā)送要更新的數(shù)據(jù)列表.

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.

誰能幫我重新創(chuàng)建存儲過程，通過檢查ID是否存在來插入數(shù)據(jù).

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

推薦答案

使用 MERGE:

根據(jù)與源表的聯(lián)接結(jié)果對目標(biāo)表執(zhí)行插入、更新或刪除操作.例如，您可以通過插入、更新或刪除行來同步兩個表在一個表中基于在另一表中發(fā)現(xiàn)的差異.

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

在我的數(shù)據(jù)表中，可以有新添加的行以及已刪除的行.那么如何比較 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;

在特定條件下從目標(biāo)中刪除數(shù)據(jù).

with specific condition to delete data from target.

這篇關(guān)于使用用戶定義的表類型在 SQL 中插入和更新的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！