MERGE keeps trying to insert null

Question

When I run this, I keep getting:

Msg 515, Level 16, State 2, Line 8
Cannot insert the value NULL into column 'uNSID', table 'Flickr.dbo.User_Grps'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

This is my SQL statement:

MERGE 
INTO User_Grps as ug
USING [ExcImport-S2 (3)] as i
ON (    
    ug.uNSID = i.uNSID
    and ug.prime_ID = i.prime_ID
    and  i.uNSID is not null
    )
WHEN MATCHED and i.uNSID is not null THEN

    UPDATE SET ug.uNSID = i.uNSID
        ,   ug.gNSID = i.gNSID
        ,   ug.gAlias = isnull(i.gAlias,ug.gAlias) 
        ,   ug.Prime_ID = i.Prime_ID

 WHEN NOT MATCHED THEN  
        INSERT (uNSID
            ,   gNSID
            ,   gAlias
            ,   Prime_ID
                )
                VALUES (i.uNSID
            ,   i.gNSID
            ,   i.gAlias
            ,   i.Prime_ID
                );
GO

What do I need to do to stop it from sucking in the null records?

Also, how would I configure counters so at the end I can get something like Updated x,xxx records & added xxx?

Edit to add --------------

@Kevin: This is my code after I modified it as I understood your response.

When I run it as is, I get the following errors:

Msg 102, Level 15, State 1, Line 2  Incorrect syntax near '$ACTION'.
Msg 137, Level 15, State 1, Line 5  Must declare the scalar variable "@InsertCount".
Msg 1087, Level 15, State 2, Line 9 Must declare the table variable "@RowCounts".

When I comment out the part between the -- ############################################
lines, I get the following error:
Msg 2627, Level 14, State 1, Line 13 Violation of PRIMARY KEY constraint 'PK_User_Grps'. Cannot insert duplicate key in object 'dbo.User_Grps'. The duplicate key value is (GT10464608)

SET NOCOUNT ON ;

DECLARE @RowCounts TABLE
        (
            MergeAction NVARCHAR(10)
        ) ;

DECLARE @InsertCount INT ,
                @UpdateCount INT;

Merge 
into User_Grps as ug
using [ExcImport-S2] as i
on (    
    ug.uNSID = i.uNSID
    and ug.prime_ID = i.prime_ID
    and  i.uNSID is not null
    )
WHEN MATCHED THEN

    UPDATE SET ug.gNSID = i.gNSID
        ,   ug.gAlias = isnull(i.gAlias,ug.gAlias) 
        ,   ug.last_ud = GETDATE ( )


 WHEN NOT MATCHED THEN  
    --      UPDATE SET @i += 1;
        INSERT (uNSID
            ,   gNSID
            ,   gAlias
            ,   Prime_ID
            ,   Last_UD
                )
                VALUES (i.uNSID
            ,   i.gNSID
            ,   i.gAlias
            ,   i.Prime_ID
            ,   GETDATE ( )
                );

    -- ############################################
 OUTPUT

In your added script there's only one `-- ###...` line, it is not clear, therefore, which part is commented when you get the error. — Andriy M, Jan 27 '13 at 00:06

Kevin Dahl · Accepted Answer · 2013-01-26T23:26:30.653

Remove the fields that you're already matching on from the update, there's no need to do that as they'd already be the same.

You can add something like this before:

SET NOCOUNT ON ;

DECLARE @RowCounts TABLE
    (
      MergeAction NVARCHAR(10)
    ) ;

DECLARE @InsertCount INT ,
    @UpdateCount INT;

And then this after immediately after the merge:

    OUTPUT
        $ACTION
        INTO @RowCounts ;

SELECT  @InsertCount = [INSERT] ,
        @UpdateCount = [UPDATE]
FROM    ( SELECT    MergeAction ,
                    1 AS ROWS
          FROM      @RowCounts
        ) P PIVOT
( COUNT(ROWS) FOR MergeAction IN ( [INSERT], [UPDATE] ) ) AS PVT ;

That'll give you your row counts.

Edit:

Link for more info on OUTPUT clause http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge.aspx

The whole shebang:

SET NOCOUNT ON ;

DECLARE @RowCounts TABLE
        (
            MergeAction NVARCHAR(10)
        ) ;

DECLARE @InsertCount INT ,
                @UpdateCount INT;

Merge 
into User_Grps as ug
using [ExcImport-S2] as i
on (    
    ug.uNSID = i.uNSID
    and ug.prime_ID = i.prime_ID
    and  i.uNSID is not null
    )
WHEN MATCHED THEN

    UPDATE SET ug.gNSID = i.gNSID
        ,   ug.gAlias = isnull(i.gAlias,ug.gAlias) 
        ,   ug.last_ud = GETDATE ( )


 WHEN NOT MATCHED THEN  
    --      UPDATE SET @i += 1;
        INSERT (uNSID
            ,   gNSID
            ,   gAlias
            ,   Prime_ID
            ,   Last_UD
                )
                VALUES (i.uNSID
            ,   i.gNSID
            ,   i.gAlias
            ,   i.Prime_ID
            ,   GETDATE ( )
                )
            OUTPUT
            $ACTION
            INTO @RowCounts ;

    SELECT  @InsertCount = [INSERT] ,
            @UpdateCount = [UPDATE]
    FROM    ( SELECT    MergeAction ,
                        1 AS ROWS
              FROM      @RowCounts
            ) P PIVOT
    ( COUNT(ROWS) FOR MergeAction IN ( [INSERT], [UPDATE] ) ) AS PVT ;

Thank you for your help, but it's still giving me dupes. Also, I'm not sure how to incorporate the counters you gave me. Please see my addition to the original post. — Deina Underhill, Jan 26 '13 at 23:11
From the OUTPUT portion to the statement terminator has to be included as a part of your merge statement as the OUTPUT clause is a part of the merge. I'll amend my answer with your query incorporated. If you're getting duplicates PK errors then one of the fields you're matching not matching on must be included in the PK (gNSID maybe?). If it's a composite key make sure you match on ALL fields involved to determine what to update and what to insert. — Kevin Dahl, Jan 26 '13 at 23:22
Hopefully this will be the final dumb question on this thread (no guarantees! :) what do I print() to get the output??? — Deina Underhill, Feb 01 '13 at 05:41
Within '@InsertCount' and '@UpdateCount' will be your numbers, you can PRINT or SELECT or INSERT them into another table however you wish. Just keep in mind that the variables are scoped to the batch level, so if you issue a GO statement you will not be able to access them afterwards. — Kevin Dahl, Feb 01 '13 at 15:19
` PRINT N'This run added ' + (cast( @InsertCount as nvarchar(6))) + ' records and updated ' + (cast( @UpdateCount as nvarchar(6))) + ' others' + N'.';` OMG, it worked!!!!!!!!!! :) — Deina Underhill, Feb 01 '13 at 21:34

score 1 · Answer 2 · answered Jan 26 '13 at 02:25

From Microsoft, you should remove your and i.uNSID is not null from your ON clause and just keep it in your WHEN clauses. That leads me to assume you need to add it to your other WHEN clause to resolve the error:

WHEN NOT MATCHED and i.uNSID is not null THEN

And here are some of the relevant notes from MS:

Specify only search conditions in the ON clause that determine the criteria for matching data in the source and target tables. That is, specify only columns from the target table that are compared to the corresponding columns of the source table.

Specify the search condition for row filtering in the appropriate WHEN clause. For example, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....

Define a view on the source or target that returns the filtered rows and reference the view as the source or target table. If the view is defined on the target table, any actions against it must satisfy the conditions for updating views. For more information about updating data by using a view, see Modifying Data Through a View.

If that doesn't fix the problem, another suggestion on their site is to use a View instead of the table and add your NULL criteria there.

Hope this helps.

Good luck.

Thanks! I'd seen that same article, but read it backwards! :( — Deina Underhill, Jan 29 '13 at 06:03

MERGE keeps trying to insert null

2 Answers2