Violation of PRIMARY KEY constraint 'PK_Fact_Logins'. Cannot insert duplicate key in object 'dbo.Fact_Logins'.

Question

Violation of PRIMARY KEY constraint 'PK_Fact_Logins'. Cannot insert duplicate key in object 'dbo.Fact_Logins'.

Rodrigo Hipolito Silva 0

Violation of PRIMARY KEY constraint 'PK_Fact_Logins'. Cannot insert duplicate key in object 'dbo.Fact_Logins'. The duplicate key value is (20230528, XV*****, 2341*****).
I have a data pipeline where the staging area is transported to the DW, the process carried out by the staging area works perfectly, but when it arrives at the DW structure, a Violation of PRIMARY KEY constraint 'PK_Fact_Logins' error appears. Cannot insert duplicate key in object 'dbo.Fact_Logins'.

I have 3 primary keys FK_Data, FK_Vendedor, [User]

these 3 keys have entries every day, as they are code information for each seller and also the date it was entered.

I already added a procedure to execute before the insertion step but it seems that it has no effect

SEE BELOW the procedure
I truncated the tables and when it happens, the insertion works, but I need to keep this data, I can't truncate it.

	

ALTER PROCEDURE [dbo].[sp_Fact_Logins] AS

BEGIN

    MERGE INTO [ESALES_DW].[dbo].[Fact_Logins] AS T

    USING (

        SELECT

            FK_Data,

            FK_Vendedor,

            [Login],

            [User],

            [Pausa]

        FROM [ESALES_SA].[dbo].[Fact_Stg_Logins] f

        INNER JOIN [ESALES_SA].[dbo].[Dim_Stg_Vendedor] v ON v.BK_Vendedor = f.FK_Vendedor

        WHERE NOT EXISTS (

            SELECT 1

            FROM [ESALES_DW].[dbo].[Fact_Logins] F

            WHERE F.[FK_Data] = f.[FK_Data]

            AND F.[FK_Vendedor] = f.[FK_Vendedor]

            AND F.[User] = f.[User]

        )

    ) AS S

    ON (

        T.[FK_Data] = S.[FK_Data]

        AND T.[FK_Vendedor] = S.[FK_Vendedor]

        AND T.[User] = S.[User]

        AND T.[Login] = S.[Login]

    )

    WHEN NOT MATCHED THEN

        INSERT (

            [FK_Data],

            [FK_Vendedor],

            [User],

            [Login],

            [Pausa]

        )

        VALUES (

            S.[FK_Data],

            S.[FK_Vendedor],

            S.[User],

            S.[Login],

            S.[Pausa]

        );

END

User's image

2 answers

Your answer

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

Your table has a three key columns, but the USING in your MERGE statement has four columns. So if your source has the key values (1,2,3) which already is in the table, but the value of login is different in source and table, your MERGE will try to insert one more rows with the same key values.

Rodrigo Hipolito Silva 0 Reputation points

2023-06-14T16:46:37+00:00

Hi @Erland Sommarskog I didn't understand very well, but how could I do this insertion?

I have this data repeatedly every day, sellers user, sales, date.

Erland Sommarskog 121.9K MVP Volunteer Moderator

I can't say how should do the insertion, because I don't know your tables or business rules. What I can do is point out what looks dubious in your query. Below is an example to clarify what I said. If you run this, you will find that it dies with a PK violation in the MERGE for the reason I said in my Answer above. Your MERGE statement has the same problem.

CREATE TABLE #target (a int NOT NULL,
                      b int NOT NULL,
                      c int NOT NULL,
                      notkey int NOT NULL,
                      othercol varchar(23) NOT NULL
                      PRIMARY KEY (a, b, c)
)
INSERT #target(a, b, c, notkey, othercol)
   VALUES(1, 1, 1, 1, 'Original row')

CREATE TABLE #source (a int NOT NULL,
                      b int NOT NULL,
                      c int NOT NULL,
                      notkey int NOT NULL,
                      othercol varchar(23) NOT NULL
                      PRIMARY KEY (a, b, c)
)
INSERT #source(a, b, c, notkey, othercol)
   VALUES(1, 1, 1, 2, 'Source row')

go
MERGE #target t
USING #source s ON t.a = s.a
               AND t.b = s.b
               AND t.c = s.c
               AND t.notkey = s.notkey
WHEN NOT MATCHED BY TARGET THEN
   INSERT (a, b, c, notkey, othercol)
      VALUES(s.a, s.b, s.c, s.notkey, s.othercol)
WHEN MATCHED THEN
  UPDATE
  SET othercol = s.othercol
;
go
DROP TABLE #source, #target

Answer 2

Hi @Rodrigo Hipolito Silva,

Violation of PRIMARY KEY constraint 'PK_Fact_Logins'. Cannot insert duplicate key in object 'dbo.Fact_Logins'. The duplicate key value is (20230528, XV***, 2341*****).**

Make sure the data source does not have duplicate values on the fields that are part of the OLEDB Destination primary key. To remove duplicates that exist on the data source you can use the Sort Transformation Task (SSIS Toolbox -> Common -> Sort) and make use of the checkbox named "Remove rows with duplicate sort values". You can find a practical example here.

You can also use the Lookup Transformation within the Data Flow task to check for data that already exist on the destination table.

lookup-transformation-in-ssis

Here is a same thread you may take a reference to dealing-with-violation-of-primary-key-constraint-ssis.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

Rodrigo Hipolito Silva 0 Reputation points

2023-06-14T16:33:33.2433333+00:00

Hi @ZoeHui-MSFT
Thanks for the answer and I added the Sort I selected the columns so that it would remove the duplicate, I did it without the columns too, but it remains not being inserted.

my problem is that

are data that end up being repeated more than once, each seller makes some sales on the day and this information is repeated on the same day, the data that changes in this case is just the login and pause

Share via

Violation of PRIMARY KEY constraint 'PK_Fact_Logins'. Cannot insert duplicate key in object 'dbo.Fact_Logins'.

2 answers

Your answer