C# · 12月 26, 2021

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

以下是我创建的新数据类型. CREATE TYPE [dbo].[UpdateHotelTableType] AS TABLE( [ID] [int] NULL,[HotelID] [int] NULL,[FromDate] [datetime] NULL,)

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

ALTER PROCEDURE [dbo].[SP_Hotel_Info_Update] — Add the parameters for the stored procedure here @XHotelInfoDetails UpdateHotelTableType READONLY,ASBEGIN Update dbo.HotelInfo SET FromDate = r.FromDate,from @XHotelInfoDetails r Where HotelInfo.ID = r.IDEND

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

任何人都可以通过检查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,ASBEGIN 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

编辑:

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;

具体条件从目标中删除数据.