使用用户定义的表类型在 SQL 插入和更新
问题说明
以下是我创建的新数据类型.
Following is new data type that I created.
CREATE TYPE [dbo].[UpdateHotelTableType] AS TABLE(
[ID] [int] NULL,
[HotelID] [int] NULL,
[FromDate] [datetime] NULL,
)
以下是我使用上述数据类型的存储过程.
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
这适用于数据库中的更新结果.但我想检查 id 是否存在，如果 id 不存在，则将该行插入表中.否则更新当前记录.在这里，我正在发送要更新的数据列表.
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.
谁能帮我重新创建存储过程，通过检查ID是否存在来插入数据.
Can any one help me to recreate the stored procedure to insert the data too by checking the existence of 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,
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
在我的数据表中，可以有新添加的行以及已删除的行.那么如何比较 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;
在特定条件下从目标中删除数据.
with specific condition to delete data from target.
这篇好文章是转载于：学新通技术网
