title: SQL Server TSQL macro
body: <p>Via SQL Server 2012, I am doing a process repeatedly for multiple databases. I am creating a new view for each database based on the tables and views I already have in each corresponding database. The new views are in the same structure, and should be all put in a new database.</p>
  
  <p>Background Info: Each database shares the same structure, same tables, same views, and same variable names for corresponding tables/views, except that the names of the databases and tables are different. And of course, the values of all the variables are different.</p>
  
  <p>For example:</p>
  
  <pre><code>Database1:
  View11:
  ID(int), Age(int), Salary(float)
  
  Database2:
  View21:
  ID(int), Age(int), Salary(float)
  
  New View:
  ID(int), Salary*0.1 as Revenue
  </code></pre>
  
  <p>My naive code:</p>
  
  <pre><code>USE Database3
  GO
  CREATE VIEW dbo.NewView11
  AS
  SELECT ID, Salary*0.1 AS Revenue
  FROM Database1.dbo.View11
  
  CREATE VIEW dbo.NewView21
  AS
  SELECT ID, Salary*0.1 AS Revenue
  FROM Database2.dbo.View21
  </code></pre>
  
  <p>the NewView11, NewView21 are in the Database3. </p>
  
  <p>I know it will be simple to copy and paste for multiple databases. But the actual number of the databases are more than 2 and the query is more complicated. So copy and paste may cause more careless errors more easily.</p>
  
  <p>Can someone share some wisdom to do it with a more accurate and standard way, rather than my clumsy copy and paste way? I am open to any strategies to solve this problem. Here are some ideas I borrowed from other languages, it may NOT work here in SQL Server. </p>
  
  <ol>
  <li>macro variable as one using in Excel.</li>
  <li>a storage function as one using in Javascript/Python/Ruby, etc.</li>
  <li>a loop.</li>
  <li>Others.
  Thanks in advance for your help!</li>
  </ol>