Databases
StackOverflow2013
Postsdbo
POWhat's wrong with this T-SQL MERGE statement?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat's wrong with this T-SQL MERGE statement?
    primarykey
    data
    text
    <p>I am new to <code>MERGE</code>, and I'm sure I have some error in my code.</p> <p>This code will run and create my scenario:</p> <p>I have two tables, one that is called <code>TempUpsert</code> that fills from a <code>SqlBulkCopy</code> operation (100s of millions of records) and a <code>Sales</code> table that holds the production data which is to be indexed and used.</p> <p>I wish to merge the <code>TempUpsert</code> table with the <code>Sales</code> one</p> <p>I am obviously doing something wrong as it fails with even the smallest example</p> <pre><code>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 </code></pre> <p>This returns:</p> <blockquote> <p>(1 row(s) affected)<br> (1 row(s) affected)<br> (1 row(s) affected) </p> <p>Msg 2627, Level 14, State 1, Procedure sp_MoveFromTempUpsert_to_Sales, Line 12<br> Violation of PRIMARY KEY constraint 'PK_Sales'. Cannot insert duplicate key in object 'dbo.Sales'. The statement has been terminated.</p> <p>(1 row(s) affected)</p> </blockquote> <p>What am I doing wrong please?</p> <p>Greatly appreciated</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOleg Dok
    1. USMickey Perlstein
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhat's wrong with this T-SQL MERGE statement?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhat's wrong with this T-SQL MERGE statement?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhat's wrong with this T-SQL MERGE statement?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe `WHEN NOT MATCHED` etc is evaluated before any inserts have happened if that is the source of the confusion? It is not the case that once you insert a matching row then future rows with the same PK will be regarded as `MATCHED`. This is required for Halloweeen Protection.
      singulars
      1. POWhat's wrong with this T-SQL MERGE statement?
      1. USMartin Smith
    2. COWhen the Match happens I wish to update the [lastupdated] field and when the match fails I wish to Insert a new record in the Sales table. I cannot pick and choose my input from the staging table (TempUpsert).
      singulars
      1. POWhat's wrong with this T-SQL MERGE statement?
      1. USMickey Perlstein
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload