title: Declare and insert a new table and insert new rows in the new table
body: <p>I have a little problem here.</p>
  
  <p>I have to declare and create a new table from already existing table. </p>
  
  <p>In the new table I have to insert new rows.</p>
  
  <p>I am working on the following code where I am able to create a new table with new rows but the other rows are missing.</p>
  
  <p>pls help me.</p>
  
  <pre><code>Declare @NOA table
  (
  [ID]    Varchar(256),
  [Groupname] Varchar(256),
  [Mis_Code]  Varchar(256),
  [Caption]   Varchar(256),
  [Amount]    Varchar(256),
  [AverageBalance]    Varchar(256),
  [Year]  Varchar(256),
  [Period]    Varchar(256),
  [Category]  Varchar(256),
  [ProductCode]  Varchar(256),
  [RevExp]    Varchar(256)
  );
  INSERT INTO @NOA
  (ID,Groupname,  Mis_Code,Caption,Amount,AverageBalance,Year,Period,Category,ProductCode,RevExp)
   Select a.[ID],'Count of existing accounts',a.[Mis_Code],a.[Caption],
   (a.[Amount] - n.[Amount]),a.[AverageBalance],a.[Year],a.[Period],a.[Category],a.[ProductCode],a.[RevExp]
   FROM (SELECT * FROM dbo.ProductSummaryMetrics WHERE [Groupname] = 'Count of active accounts') a
    JOIN (SELECT * FROM dbo.ProductSummaryMetrics WHERE [Groupname] = 'Count of new account opened (MTD)') n
      ON a.[Mis_Code] = n.[Mis_Code]
      select distinct Groupname from @NOA
  </code></pre>
  
  <p>with the above code 
  I get the output as</p>
  
  <pre><code>Groupname 
  --------------------------
  Count of existing accounts  
  </code></pre>
  
  <p>Where as I would like to get all the group names like this</p>
  
  <pre><code>Groupname 
  --------------------------
  Count of existing accounts 
  Count of active accounts
  Count of new account opened (MTD)
  </code></pre>