tsql - 这个 T-SQL MERGE 语句有什么问题？

Question

我是新来的 MERGE ，而且我确定我的代码中有一些错误。

此代码将运行并创建我的场景:

我有两张 table ，一张叫 TempUpsert从 SqlBulkCopy 填充操作(数百万条记录)和 Sales保存要索引和使用的生产数据的表。

我想合并TempUpsert与 Sales 的表一

我显然做错了什么，因为即使是最小的例子也失败了

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempUpsert]') )
drop table TempUpsert;

CREATE TABLE [dbo].[TempUpsert](
      [FirstName] [varchar](200) NOT NULL,
      [LastName] [varchar](200) NOT NULL,
      [Score] [int] NOT NULL
) ON [PRIMARY] ;

CREATE TABLE [dbo].[Sales](
      [FullName] [varchar](200) NOT NULL,
      [LastName] [varchar](200) NOT NULL,
      [FirstName] [varchar](200) NOT NULL,
      [lastUpdated] [date] NOT NULL,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED 
(
      [FullName] ASC
)

---- PROC

CREATE PROCEDURE  [dbo].[sp_MoveFromTempUpsert_to_Sales]
(@HashMod int)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

MERGE Sales AS trget
    USING (

    SELECT 
--- Edit: Thanks to Mikal added DISTINCT
DISTINCT
            FirstName, LastName , [Score], LastName+'.'+FirstName  AS FullName
    FROM TempUpsert AS ups) AS src (FirstName, LastName, [Score], FullName)

    ON 
    (
            src.[Score] = @hashMod 
    AND 
            trget.FullName=src.FullName
    )

    WHEN MATCHED 
        THEN 

        UPDATE SET trget.lastUpdated = GetDate() 

      WHEN NOT MATCHED 
            THEN        INSERT   ([FullName], [LastName], [FirstName], [lastUpdated]) 
      VALUES (FullName, src.LastName, src.FirstName, GetDate())

   OUTPUT $action, Inserted.*, Deleted.* ;
      --print @@rowcount

END

GO

---  Insert dummie data

INSERT INTO TempUpsert (FirstName, LastName, Score)
VALUES ('John','Smith',2);


INSERT INTO TempUpsert (FirstName, LastName, Score)
VALUES ('John','Block',2);


INSERT INTO TempUpsert (FirstName, LastName, Score)
VALUES ('John','Smith',2); --make multiple on purpose

----- EXECUTE PROC
GO


DECLARE     @return_value int

EXEC  @return_value = [dbo].[sp_MoveFromTempUpsert_to_Sales]
            @HashMod = 2

SELECT      'Return Value' = @return_value
GO

这将返回:

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Msg 2627, Level 14, State 1, Procedure sp_MoveFromTempUpsert_to_Sales, Line 12
Violation of PRIMARY KEY constraint 'PK_Sales'. Cannot insert duplicate key in object 'dbo.Sales'. The statement has been terminated.

(1 row(s) affected)

请问我做错了什么？

不胜感激

Answer 1

临时表中的前两行将为您提供重复的 PK。违反。 Conc 是 PK 并且您两次插入具有相同值的 tmain+dmain。