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

Insert and Update in SQL Using User-Defined Table Type(使用用户定义的表类型在 SQL 中插入和更新)
本文介绍了使用用户定义的表类型在 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.

这篇关于使用用户定义的表类型在 SQL 中插入和更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
SSIS: Model design issue causing duplications - can two fact tables be connected?(SSIS:模型设计问题导致重复-两个事实表可以连接吗?)
SQL Server Graph Database - shortest path using multiple edge types(SQL Server图形数据库-使用多种边类型的最短路径)
Invalid column name when using EF Core filtered includes(使用EF核心过滤包括时无效的列名)
How should make faster SQL Server filtering procedure with many parameters(如何让多参数的SQL Server过滤程序更快)
How can I generate an entity–relationship (ER) diagram of a database using Microsoft SQL Server Management Studio?(如何使用Microsoft SQL Server Management Studio生成数据库的实体关系(ER)图?)