Insert and Update in SQL Using User-Defined Table Type
You can Simply do 2 Queries: 1. "Update" command 2. "Insert" command.
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
Use 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
EDIT:
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?
You could add new clause:
WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN DELETE;
with specific condition to delete data from target.