如何使用不同的@table 类型或从自定义用户表类型中删除重复项? [英] How to Using distinct @table type or remove duplicate from custom user table type?

问题描述

我使用传递表类型来存储批量插入和更新(合并)的过程.但是如您所知，在 Merge 语句中，所有记录都插入一个事务，例如，如果我的自定义 @table 类型中有 2 个重复记录而不是插入其中之一，则插入 2 个重复记录 ...现在我想在插入之前合并@table 类型的记录.在我的程序案例中，我无法合并超过 1M 的记录，因为我遇到了内存异常.然而，这是我的 SP 代码:

I use pass table type to store procedure for bulk insert and update (Merge). But as you know , in Merge statement all records insert with one transaction , for example if i have 2 duplicate record in my custom @table type instead of insert one of them , insert 2 duplicate Record ... Now I want to merge @table type records before insert them . in my program case I cant merge more than 1M record because i get memory exception. However this is my SP code :

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[Mysp_MergeInsert_tblRelationship]
      @tblrelationship TBLRELATIONSHIPTYPE READONLY AS
    BEGIN
        SET NOCOUNT ON;
        MERGE INTO tblRelationship tM
        USING @tblrelationship tS
        ON tM.MasterUserId = tS.MasterUserId
       AND tM.userId = tS.userId
        WHEN MATCHED
              THEN UPDATE SET tM.Follows = tS.Follows,
                              tM.FollowBy = tS.FollowBy
        WHEN NOT MATCHED
              THEN INSERT VALUES( tS.MasterUserId, tS.userId, tS.Follows, tS.FollowBy );
    END;

我可以用这个 sql 代码删除重复项，但当@tblrelationship 为只读时不能，当@tblrelationship 为非只读时不能使用合并

I can delete duplicate with this sql code , but not when @tblrelationship is readonly , and cannot use merge when @tblrelationship is Not readonly

WITH CTE AS(
        SELECT *,
        RN = ROW_NUMBER()OVER(PARTITION BY userId ORDER BY userId)
        FROM @tblrelationship
        )
        delete FROM CTE WHERE RN > 1

你知道有什么解决办法吗?谢谢.

did you know any solution to solve it ? Thanks .

推荐答案

为什么不使用带有 DISTINCT 的简单子查询来进行合并?应该可以.

Why don't you use a simple subquery with DISTINCT in it to do merge? Should work.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC dbo.Mysp_MergeInsert_tblRelationship
(
  @tblrelationship TBLRELATIONSHIPTYPE READONLY AS
)
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    MERGE INTO tblRelationship tM
    USING (
      SELECT DISTINCT UserId, MasterUserId, Follows, FollowBy
      FROM @tblrelationship
      ) tS
    ON tM.MasterUserId = tS.MasterUserId
      AND tM.userId = tS.userId
    WHEN MATCHED
        THEN UPDATE SET tM.Follows = tS.Follows,
                tM.FollowBy = tS.FollowBy
    WHEN NOT MATCHED
        THEN INSERT VALUES( tS.MasterUserId, tS.userId, tS.Follows, tS.FollowBy);
  END TRY
  BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
  END CATCH
END

附言我已将 TRY/CATCH 添加到您的程序中.建议这样做.在这种情况下，CATCH 语句是一个简单的 SELECT，但您可以将错误写入一些审计表中以跟踪它们.

P.S. I've added TRY/CATCH to your procedure. It's recommended practice to do that. In this case CATCH statement is a simple SELECT, but you could write errors into some Audit table in order to track them.

这篇关于如何使用不同的@table 类型或从自定义用户表类型中删除重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！