SQL/C# - Primary Key error on UPSERT

Question

UPDATE(simplified problem, removed C# from the issue)

How can I write an UPSERT that can recognize when two rows are the same in the following case...

SSMS

See how there's a \b [backspace] encoded there (the weird little character)? SQL sees these as the same. While my UPSERT sees this as new data and attempts an INSERT where there should be an UPDATE.

//UPSERT
    INSERT INTO [table]
    SELECT [col1] = @col1, [col2] = @col2, [col3] = @col3, [col4] = @col4
    FROM [table]
    WHERE NOT EXISTS
       -- race condition risk here?
       ( SELECT 1 FROM [table] 
       WHERE 
            [col1] = @col1 
        AND [col2] = @col2
        AND [col3] = @col3)

    UPDATE [table]
        SET [col4] = @col4
        WHERE 
        [col1] = @col1 
        AND [col2] = @col2
        AND [col3] = @col3

Please note that I want to know why SQL is throwing a primary key exception. I am not looking for an explanation on C# escaping od strings. — P.Brian.Mackey, Aug 31 '11 at 22:45
You want to know about a specific error, yet you have no error message in your question. Words like "barf" don't convey enough meaning. You also say that you see the "jacked up character" in the database, so that means the row was inserted, right? If it was a PK violation, there would be no row in the database. Please edit your question to contain at least the error message and specifically what operation caused it to be thrown. — Tadmas, Aug 31 '11 at 23:36
@Tadmus - i can do that at work tomorrow. Though, given the title and the behavior of an upsert I dont think it will be of any help. If anything, the Stored Proc upsert code is the only useful missing info. — P.Brian.Mackey, Sep 01 '11 at 00:11
The PK is a combination of UserID varchar(100), PageName varchar(100), ItemID varchar(100). The only other column is Value varchar(250) null, no constraint. — P.Brian.Mackey, Sep 01 '11 at 13:56

score 4 · Answer 1 · answered Aug 31 '11 at 21:30

4

You need the @ sign, otherwise a C# character escape sequence is hit.

C# defines the following character escape sequences:

\' - single quote, needed for character literals 
\" - double quote, needed for string literals 
\\ - backslash 
\0 - Unicode character 0 
\a - Alert (character 7) 
\b - Backspace (character 8) 
\f - Form feed (character 12) 
\n - New line (character 10) 
\r - Carriage return (character 13) 
\t - Horizontal tab (character 9) 
\v - Vertical quote (character 11) 
\uxxxx - Unicode escape sequence for character with hex value xxxx 
\xn[n][n][n] - Unicode escape sequence for character with hex value nnnn (variable length version of \uxxxx) 
\Uxxxxxxxx - Unicode escape sequence for character with hex value xxxxxxxx (for generating surrogates)

answered Aug 31 '11 at 21:30

Jon Raynor

3,804
6
29
43

1

I dont see how this helps explain the PK exception thrown by SQL..? Shouldnt there simply be two different rows rather than an Exception? In other words two successful inserts. – P.Brian.Mackey Aug 31 '11 at 22:22
@Brain - The unicode escape sequence may be causing something odd with the database. If the only difference between tests is the @ sign, then that points to the issue. Whats saved in the database table(s) as far as records? A PK exception would be a unique violation, so I would check the keys (columns) in the database table versus what is being sent in. You should find a duplicate value on the insert which would be causing the violation. – Jon Raynor Sep 01 '11 at 00:36

score 2 · Accepted Answer · edited May 23 '17 at 12:06

After hours of tinkering it turns out I've been on a wild goose chase. The problem is very simple. I pulled my UPSERT from a popular SO post. The code is no good. The select will sometimes return > 1 rows on INSERT. Thereby attempting to insert a row, then insert the same row again.

The fix is to remove FROM

    //UPSERT
    INSERT INTO [table]
    SELECT [col1] = @col1, [col2] = @col2, [col3] = @col3, [col4] = @col4
    --FROM [table] (Dont use FROM..not a race condition, just a bad SELECT)
    WHERE NOT EXISTS
       ( SELECT 1 FROM [table] 
       WHERE 
            [col1] = @col1 
        AND [col2] = @col2
        AND [col3] = @col3)

    UPDATE [table]
        SET [col4] = @col4
        WHERE 
        [col1] = @col1 
        AND [col2] = @col2
        AND [col3] = @col3

Problem is gone.

Thanks to all of you.

score 1 · Answer 3 · answered Sep 01 '11 at 13:32

1

You are using '\u' which generates a Unicode character.

Your column is a varchar, which does not support Unicode characters. nvarchar would support the character.

answered Sep 01 '11 at 13:32

Amy B

108,202
21
135
185

SQL/C# - Primary Key error on UPSERT

3 Answers3

Linked