Geek asks and answers

Paste into table if record does not exist yet

For some reason this gives me the error "cannot insert duplicate record into table".

INSERT  INTO [DMS].[dbo].[Deductions]
        (
         CustomerID,
         DeductionCode,
         DeductionDescription
        )
        SELECT  b.CustomerID,
                b.AdjustmentReason,
                b.AdjustmentReason
        FROM    @CreditDebitAdjustmentDetail b
        WHERE   NOT EXISTS ( SELECT 1
                             FROM   [DMS].[dbo].[Deductions]
                             WHERE  CustomerID = b.CustomerID
                                    AND DeductionCode = b.AdjustmentReason )

Strange, I tested this as such:

DECLARE @CreditDebitAdjustmentDetail TABLE
        (
         CustomerID INT,
         AdjustmentReason VARCHAR(50)
        )

INSERT  INTO @CreditDebitAdjustmentDetail
        (CustomerID, AdjustmentReason)
VALUES  (143, -- CustomerID - int
         '024'  -- AdjustmentReason - varchar(50)
         )

INSERT  INTO [DMS].[dbo].[Deductions]
        (
         CustomerID,
         DeductionCode,
         DeductionDescription
        )
        SELECT  b.CustomerID,
                b.AdjustmentReason,
                b.AdjustmentReason
        FROM    @CreditDebitAdjustmentDetail b
        WHERE   NOT EXISTS ( SELECT 1
                             FROM   [DMS].[dbo].[Deductions]
                             WHERE  CustomerID = b.CustomerID
                                    AND DeductionCode = b.AdjustmentReason )

AND DOES NOT Insert into the table because the record already exists.

Did I miss something?

EDIT - I thought I fixed it by doing this, but I still get the same error:

INSERT  INTO [DMS].[dbo].[Deductions]
        (
         CustomerID,
         DeductionCode,
         DeductionDescription
        )
        SELECT  a.CustomerID,
                a.AdjustmentReason,
                a.AdjustmentReason
        FROM    @CreditDebitAdjustmentDetail a
        WHERE   NOT EXISTS ( SELECT 1
                             FROM   [DMS].[dbo].[Deductions] b
                             WHERE  a.CustomerID = b.CustomerID
                                    AND a.AdjustmentReason = b.DeductionCode )
+3
source share
1 answer

I get it, DOH!

KEYWORD ... DISTINCT -_-

INSERT INTO [DMS].[dbo].[Deductions]
                    (
                     CustomerID,
                     DeductionCode,
                     DeductionDescription
                    )
                    SELECT  DISTINCT
                            a.CustomerID,
                            ISNULL(a.AdjustmentReason, 'UNKNOWN'),
                            ISNULL(a.AdjustmentReason, 'UNKNOWN')
                    FROM    @CreditDebitAdjustmentDetail a
                    WHERE   NOT EXISTS ( SELECT 1
                                         FROM   [DMS].[dbo].[Deductions] b
                                         WHERE  a.CustomerID = b.CustomerID
                                                AND CASE a.AdjustmentReason
                                                      WHEN NULL THEN 'UNKNOWN'
                                                      WHEN '' THEN 'UNKNOWN'
                                                    END = b.DeductionCode )
+2
source

More articles:


All Articles