Insert and update in SQL using a custom table type

Question

Below is the new data type that I created.

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

)

Below is my stored procedure in which I used the above data type.

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

This works great for update results in a database. But I want to check if id exists, and if id does not exist, insert a row into the table. otherwise update the current record. Here I submit a list of data to update.

Can someone help me recreate the stored procedure to insert data by checking for an identifier.

Answer 1

Use : MERGE

, . , , , , .

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

:

, . hotelinfo?

:

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

.

Answer 2

2 : 1. "" 2. "".

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 dbo.HotelInfo 
    JOIN @XHotelInfoDetails X ON X.Id = HotelInfo.Id

    INSERT INTO dbo.HotelInfo (Col1,Col2)
    SELECT X.Col1,
           X.Col2
    FROM @XHotelInfoDetails X
    WHERE NOT EXISTS
                   (
                     SELECT 1
                     FROM dbo.HotelInfo InnerTable
                     WHERE X.Id = InnerTable.Id
                   )

END