What happened to this MERGE T-SQL statement?

Question

I am new to MERGE and I am sure that I have some error in my code.

This code will run and create my script:

I have two tables, one of which is called TempUpsert , which is populated from the SqlBulkCopy operation (100 million million records) and the Sales table, which contains production data that must be indexed and used.

I want to combine the TempUpsert table with Sales one

I obviously am doing something wrong as it fails even with the smallest example

 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 

This returns:

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

Msg 2627, Level 14, State 1, Procedure sp_MoveFromTempUpsert_to_Sales, Line 12
Violation of PRIMARY KEY "PK_Sales" restriction. Cannot insert duplicate key in object 'dbo.Sales'. Application completed.

(1 row (s) affected)

What am I doing wrong?

Very much appreciated

Answer 1

The first two rows in your staging table will give you a duplicate of the PC. violation. Conc is PK, and you insert tmain + dmain with the same value twice.

Answer 2

In summation

MERGE requires that its input (use) be duplicated. Use is a regular SQL statement, so you can use Group By, different, and having, as well as Where clauses.

My final merge looks like this:

 MERGE Sales AS trget USING ( SELECT FirstName, LastName, Score, LastName + '.' + FirstName AS FullName FROM TempUpsert AS ups WHERE Score = @hashMod GROUP BY FirstName, LastName, Score, LastName + '.' + FirstName ) 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 

And it works!

Thank you all:)

Answer 3

Without the DISTINCT function or the proper AGGREGATE function in the subquery used in the USE of the MERGE part, there will be two rows that meet the criteria used in the included MERGE part, which is unacceptable. (Two John. Smith)

AND

Move the src.[Score] = @hashMod condition src.[Score] = @hashMod inside the subquery,

instead, if the ON clause is unsuccessful, for example, John.Smith has a score of 2, and @HashMod = 1 - then if you already have a row with John.Smith in the target table, you will receive an error message with Primary Key Constraint