title: SQL query to Copy rows by incrementing ID based on category
body: <p>I have a table having two columns </p>
  
  <p>If the category name is same, category id will be same </p>
  
  <pre><code>CatID CatName
  
  1       Cat1
  1       Cat1
  1       Cat1
  2       Cat2
  2       Cat2
  3       Cat3
  3       Cat3
  3       Cat3
  </code></pre>
  
  <p>I want to copy the above table rows to the same table to get result like below </p>
  
  <pre><code>CatID CatName
  1       Cat1
  1       Cat1
  1       Cat1
  2       Cat2
  2       Cat2
  3       Cat3
  3       Cat3
  3       Cat3
  4       Cat1
  4       Cat1
  4       Cat1
  5       Cat2
  5       Cat2
  6       Cat3
  6       Cat3
  6       Cat3
  </code></pre>
  
  <p>Where CatID 4 is the top+1 of CATID</p>
  
  <p>I know, i can use the followng query:</p>
  
  <pre><code> insert into [table1]  
   select (SELECT MAX([CatID]) from [table1]) +
   ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as [CatID], CatName 
   from [table1]
  </code></pre>
  
  <p>which give result like below, that i dont want.</p>
  
  <pre><code>CatID CatName
  
  4       Cat1
  5       Cat1
  6       Cat1
  7       Cat2
  8       Cat2
  9       Cat3
  10      Cat3
  11      Cat3
  </code></pre>
  
  <p>Here, CatID is incrementing, i need to increment catid based on category.</p>