title: How to UPDATE or Insert CTE in a table. SQL
body: <p>Hello Guys I having been trying to insert the result of the following code in a column called dbo.filemetadata.Source, here is the Code:</p>
  
  <pre><code>WITH CTE AS 
  (
    SELECT 
      rn, 
      ROW_NUMBER() OVER (PARTITION BY rn ORDER BY (SELECT  NULL)) filepartno,
      Split.a.value('.', 'VARCHAR(100)') AS filepart  
    FROM  
    (
      SELECT ROW_NUMBER() OVER (ORDER BY (SELECT  NULL)) rn,
       CAST ('&lt;M&gt;' + REPLACE(FilePath, '\', '&lt;/M&gt;&lt;M&gt;') + '&lt;/M&gt;' AS XML) AS String  
      FROM  FileMetadata
    ) AS A 
    CROSS APPLY String.nodes ('/M') AS Split(a)
  )
  SELECT  FilePart AS A
  
  FROM CTE C
    JOIN (SELECT  MAX(filepartno) maxfilepartno, rn 
          FROM CTE 
          GROUP BY rn) C2 ON C.rn = C2.rn AND C.filepartno = C2.maxfilepartno-1;
  </code></pre>