Sql/c# - Primary Key Error On Upsert

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... See how there's a \b [bac

Solution 1:

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) 

Solution 2:

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
    INSERTINTO [table]
    SELECT [col1] =@col1, [col2] =@col2, [col3] =@col3, [col4] =@col4--FROM [table] (Dont use FROM..not a race condition, just a bad SELECT)WHERENOTEXISTS
       ( SELECT1FROM [table] 
       WHERE 
            [col1] =@col1AND [col2] =@col2AND [col3] =@col3)

    UPDATE [table]
        SET [col4] =@col4WHERE 
        [col1] =@col1AND [col2] =@col2AND [col3] =@col3

Problem is gone.

Thanks to all of you.

Solution 3:

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.