title: Looping over columns and changing Null values
body: <p>I have a table called myTable where some values are null.</p>
  <p><a href="https://i.stack.imgur.com/w8MGL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/w8MGL.png" alt="enter image description here" /></a></p>
  <p>I want to replace all null values in all columns with the previous non null value. I found some code that iterates over each row for a specific column, and changes Null Values as I want.</p>
  <pre><code>DECLARE @value AS int
  UPDATE myTable 
  SET
     @value = COALESCE(col2, @value),
     col2 = COALESCE(col2, @value) 
  </code></pre>
  <p>Result:</p>
  <p><a href="https://i.stack.imgur.com/o3XXv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/o3XXv.png" alt="enter image description here" /></a></p>
  <p>This does what I want it do do but only for one column at the time. My goal is to alter the code above in some way so that I can automatically loop over each column in the table.</p>
  <p>I ran into several issues when trying to achieve this. Here is my attempt</p>
  <pre><code>DECLARE @ColNames table (NAMES nvarchar(50), ARRAYINDEX int identity(1,1) )  
  INSERT INTO @ColNames (NAMES)  
  VALUES ('col1'),('col2'),('col3')
  
  DECLARE @INDEXVAR int  
  DECLARE @TOTALCOUNT int  
  SET @INDEXVAR = 0  
  SELECT @TOTALCOUNT = COUNT(*) FROM @ColNames
  
  WHILE @INDEXVAR &lt; @TOTALCOUNT 
  BEGIN  
      DECLARE @curColName nvarchar (50)  
      SELECT @INDEXVAR = @INDEXVAR + 1  
      SELECT @curColName = NAMES from @ColNames where ARRAYINDEX = @INDEXVAR  
      DECLARE @value AS int
      UPDATE myTable 
      SET
          @value = COALESCE(@curColName, @value),
          @curColName = COALESCE(@curColName, @value)
  END 
  </code></pre>
  <p>The issues that I have found and not been able to solve are the following:</p>
  <ol>
  <li>@curColName is just a nvarchar variable and not a representation of my actual column, even if the names are the same. This gives me errors on both lines inside the SET statement.</li>
  <li>When hard coding the column names in my loop inside the BEGIN/END statement., the script fills out ALL Null values with a number. So col2 gets the value 3 on ALL rows, not only row 2 and 3 as my previous example.</li>
  </ol>
  <p>If these two points are hard or impossible to solve, is there an easier way of solving this problem?</p>
  <p>Thanks</p>