Violation of PRIMARY KEY constraint, Cannot insert

问题:

I have a table as below:

|Bookname|BDate|Description

and Bookname is my Primary Key I need to read a file regularly to update my table. I have writen a Stored Procedure to update the file as below:

Create PROCEDURE [dbo].[UpdateMyTable] 
    -- Add the parameters for the stored procedure here
    @SourceTable AS DVTable READONLY
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    MERGE dbo.MyTarget AS T
    USING @SourceTable AS S ON (T.BookName=S.BookName)
    WHEN NOT MATCHED BY Target
    THEN INSERT(BookName,BDate,Description) 
    VALUES(S.BookName,S.BDate,S.Description)
    WHEN MATCHED
    THEN UPDATE SET T.BookName=S.BookName,T.BDate=S.BDate,T.Description=S.Description;

END

There is a problem that inside the text file some records repeated twice, so the merge function inserts the file at first and for the second time throw exception: [Violation of PRIMARY KEY constraint]

I was wondering if there is any way to set MERGE to ignore it if find that Bookname for the second time.

回答1:

If your source table has two rows with the same BookName that doesn't exist in Target table, you could use CTE like that

;WITH temp AS
(
   SELECT st.* , 
          row_number() over(PARTITION BY st.BookName ORDER BY BookDate desc)  as Rn 
   FROM @SourceTable st
)
MERGE dbo.MyTarget AS T
USING (SELECT * FROM temp t WHERE Rn = 1) AS S ON (T.BookName=S.BookName)
............

回答2:

Before the merge, try deleting the duplicate records from the table @SourceTable based on BDate. The below query will delete the bookname with lower booking date and keep the highest booking date for that Book so you can have unique BookName.

;with cte (BookName,BDate,Description,RowNum)AS
(

Select BookName,BDate,Description,Row_number() over(partition by BookName  order by BDate desc)
 from @SourceTable
)
delete from cte where rownum>1