問題描述

以下是我創(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ù)類型的存儲(chǔ)過程.

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)建存儲(chǔ)過程，通過檢查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é)果對(duì)目標(biāo)表執(zhí)行插入、更新或刪除操作.例如，您可以通過插入、更新或刪除行來同步兩個(gè)表在一個(gè)表中基于在另一表中發(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 中插入和更新的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！