title: MERGE, UPDATE, INSERT T-SQL Cannot INSERT value NULL
body: <p>Goal: Keep a running table of student class ranks each month of the year</p>
  
  <p>Haves: I have code that provides me with columns</p>
  
  <pre><code>StudentID; '+@DateTXT+'
  </code></pre>
  
  <p>The DateTXT is dynamic variable, returns whatever month I'm running the code in.</p>
  
  <p>Needs: I'm trying to use the MERGE, UPDATE, INSERT functions to where I can run the code once and establish a table:</p>
  
  <pre><code>| StudentID | Jan |
  |  56789    |  2  |
  |  12345    |  7  |
  </code></pre>
  
  <p>Then each month I add a new month column the permanent table:</p>
  
  <pre><code>EXEC('ALTER TABLE StudentRanking
       ADD ' + @DateTXT + ' smallint NOT NULL DEFAULT(999)')
  
  | StudentID | Jan | Feb |
  |  56789    |  2  | 999 |
  |  12345    |  7  | 999 |
  </code></pre>
  
  <p>I'll run the ranking code again for February and save it into a temporary table, which I will use to merge, update, insert with the StudentRanking table:</p>
  
  <pre><code>| StudentID | Feb |
  |  56789    |  3  |
  
  (note.. student 12345 doesn't come up)
  </code></pre>
  
  <p>So I'd like to end up with a running list:</p>
  
  <pre><code>EXEC('
    MERGE StudentRanking AS TARGET
    USING ##TEMPDB2 AS SOURCE ON (TARGET.StudentID = SOURCE.StudentID)
  
    WHEN MATCHED AND TARGET.' + @DateTXT + ' &lt;&gt; SOURCE.' + @DateTXT + '
    THEN UPDATE SET TARGET.' + @DateTXT + ' = SOURCE.' + @DateTXT + '
  
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (StudentID, ' + @Rank_TXT + ')
    VALUES (SOURCE.StudentID, SOURCE.' + @Rank_TXT + ') ')
  
  | StudentID | Jan | Feb |
  |  56789    |  2  |  3  |
  |  12345    |  7  |null |
  </code></pre>
  
  <p>Problem: Some students leave the school, thereby creating a null ranking in proceeding months (e.g. 12345 has no rank in February), so when I try to INSERT the results from a temporary table, I get this ERROR:</p>
  
  <pre><code>SQL Server Database Error: Cannot insert the value NULL into column 'Feb', table 'tempdb.dbo.##TEMPDB'; column does not allow nulls. UPDATE fails.
  </code></pre>
  
  <p>I could do an ISNULL(ranking,0) but I'd rather have nulls than 0's</p>