title: Select from a DB2 table and insert to a SQL table
body: <p>I feel like this might be a crazy question, and if anyone has a better idea of how to do it, by all means please let me know. </p>
  
  <p>I have a .Net C# project at work that validates certain tables against the company standards they must adhere to.  The tables can be on SQL Server or DB2.  When a table is initially added to the program, I gather the metadata/information about the table using select statements off of either <code>information_schema.columns</code> for sql, or <code>syscat.columns</code> for db2.  Once I get that data, I store it in a table (let's call it <code>all_table_information</code>) on sql server containing that same information for every table that is validated in the program.  </p>
  
  <p>If the table is a SQL table, I can run this query (of course restricting it to only the columns from the table I want):</p>
  
  <pre><code>insert into [all_table_information]
      (table_id, column_name, data_type, max_char_length)
  select table_id, column_name, data_type, character_maximum_length
      from information_schema.columns 
      where ...restrict to needed table...
  </code></pre>
  
  <p>And then execute that in a SqlCommand.  But if it is a DB2 table, I have to run this query (restricted again to the columns I need):</p>
  
  <pre><code>select tabschema, tabname, colname, typename, length 
  from syscat.columns 
  where ...restrict to needed table...
  </code></pre>
  
  <p>Then get a DataReader with the results and loop through it inserting each row using:</p>
  
  <pre><code>while (dr.Read())
  {
     insert into [all_table_information]
     (table_id, column_name, data_type, max_char_length) values 
     (..."'" + dr["whatever"] + "', '" + ....)
  
     ...execute sql here...
  
  } 
  dr.Close();
  </code></pre>
  
  <p>Doing it this way works, but it is slow, so I was just wondering if there was anyway to do this in one statement?  I know you could save the db2 table information to a data table as well, but can you run a query against a data table directly?</p>
  
  <p>Thanks,
  Ryan</p>