title: Use SELECT results as a variable in a loop
body: <p>I've searched here and elsewhere, and haven't found an answer yet. Hope I didn't miss it.</p>
  
  <p>Using SQL Server Management Studio 2008 R2.</p>
  
  <p>I have n specific databases on my server (there are other DBs as well, but I'm only interested in some of them)  </p>
  
  <p>Each of these databases has a table within it, which all have the same name. The only difference is the DB name. I want to aggregate these tables together to make one big table on a different database (different to the other DBs).</p>
  
  <p>I can get the db names from the results of a query.</p>
  
  <p>N is unknown.</p>
  
  <p>Is a loop the way to go about this?  </p>
  
  <p>I was thinking something along the lines of the following pseudocode:</p>
  
  <pre><code>Set @dbnames = SELECT DISTINCT dbname FROM MyServer.dbo.MyTable
  
  For each @name in @dbnames
      INSERT INTO ADifferentDB.dbo.MyOtherTable
      SELECT * FROM @name.dbo.table
  Next name
  </code></pre>
  
  <p>(Clearly I'm new to using SQL variable as well, as you can see)</p>