代码之家 › 专栏 › 技术社区 › Amiel Echizen

如何使用存储过程避免重复记录[重复]

stored-procedures sql-server

-1

Amiel Echizen · 技术社区 · 7 年前

我想编写一个存储过程,将数据插入表中,并检查是否已经存在相同的数据?

如果是,则异常抛出为已存在。但我不知道应该在哪里添加例外。请帮忙。

ALTER PROCEDURE [dbo].[datakutipantest1]
AS
BEGIN
    DECLARE @ModifiedDate datetime = GETDATE()

    INSERT INTO spk_DataKutipan ([NO_BIL], [NO_AKAUN], [TKH_BAYAR],
  [STESYEN], [AMAUN_BAYAR], [JENIS_BAYAR], [NO_RESIT], [STATUS], [NO_VOT], [TKH_MODIFIKASI])
        SELECT
            D.BillNo,
            D.AccountNo,
            D.TxDate,
            D.ReferenceCode,
            D.Amount,
            PaymentTypeId,
            D.ReferenceNo,
            D.Status,
            D.RevenueCode,
            @ModifiedDate
        FROM 
            (SELECT
                 B.ComponentId,
                 B.AccountNo,
                 B.BillNo,
                 B.RevenueCode,
                 B.Amount,
                 B.TxId,
                 ReferenceNo,
                 B.ReferenceCode,
                 status,
                 TxDate
             FROM 
                 (SELECT
                      A.ComponentId,
                      A.TxId,
                      AccountNo,
                      BillNo,
                      RevenueCode,
                      Amount,
                      C.ReferenceCode
                  FROM 
                      rcs_TxBillItem A
                  INNER JOIN 
                      (SELECT
                           ComponentId,
                           ComponentName,
                           ReferenceCode
                       FROM 
                           rcs_Component
                       WHERE  
                           IsDeleted = 0) C ON C.ComponentId = A.ComponentId) B
                  INNER JOIN  
                      rcs_TxBill P ON P.TxId = B.TxId) D

             INNER JOIN 
                 rcs_TxBillPayment E ON E.TxId = D.TxId
END
GO

2 回复 | 直到 7 年前

1

Naushad 7 年前

CREATE PROCEDURE [dbo].[Useradd]
      @username varchar(20),
      @pword nvarchar(20),
      @empname varchar(20),
      @email nvarchar(50),
      @designation varchar(20),
      @reportto varchar(20) AS
  IF (SELECT COUNT(*) FROM Users WHERE username= @username) < 1
    BEGIN   
      INSERT INTO Users(username,pword,empname,email,designation,reportto)    
      VALUES(@username,@pword,@empname,@email,@designation,@reportto)
    END

2

0

Alfin E. R. 7 年前

考虑使用合并

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition> --For example ColumnSource = ColumnTable
WHEN MATCHED --Rows already exist
   THEN UPDATE target_table SET ... --Do Update for example
WHEN NOT MATCHED BY TARGET --Rows not exist on Target Table
   THEN INSERT(...) VALUES(...) --Do Insert for example
WHEN NOT MATCHED BY SOURCE --Rows not exist on Source table but exists on Target Table
   THEN DELETE ... --Do Delete for example

MERGE语句基本上是根据指定的条件以及目标中是否已经存在来自源的数据,将源结果集中的数据合并到目标表中。新的SQL命令将条件插入、更新和删除命令的序列组合在一个原子语句中,具体取决于记录的存在。