title: SQLite CREATE TABLE AS but rename columns and keep original data types, keys?
body: <p><strong>SQLite Objective</strong></p>
  
  <p>I would like to create a new table from a join (or series of joins/subqueries). I would like the new table to preserve the data types in the original tables as well as the unique index. </p>
  
  <p>In addition, I would like to rename some of the columns in the process. To do this in SQLite, I am under the impression that I need to do the following steps:</p>
  
  <blockquote>
    <ol>
    <li>Create the joined table as a temporary table</li>
    <li>Create an empty new table with the desired column names, data types, and index</li>
    <li>Insert the temporary table into the new table.</li>
    </ol>
  </blockquote>
  
  <p><strong>Question: Is there a better or more succinct way to accomplish this task?</strong></p>
  
  <p><em>Code example of above process:</em></p>
  
  <pre class="lang-sql prettyprint-override"><code>--create joined table as tmp
  DROP TABLE if exists tmp;
  
  CREATE TABLE tmp AS
  SELECT 
      individual_contributions.cmte_id, 
      cmte_pty_affiliation, 
      name, 
      employer, 
      transaction_dt,
      transaction_amt,
      file_num,
      cand_id,
      sub_id 
      FROM individual_contributions LEFT JOIN committee_master 
      ON individual_contributions.cmte_id=committee_master.cmte_id
      WHERE employer LIKE "Goldman Sachs"
      GROUP BY sub_id;
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>--create new table with desired specs
  DROP TABLE if exists cm_indiv;
  
  CREATE TABLE cm_indiv (
      cmte_id TEXT NOT NULL,
      cmte_pty_affiliation TEXT,
      contributor_name TEXT,
      contributor_employer TEXT,
      contributor_transaction_dt TEXT,
      cand_id TEXT,
      transaction_amt TEXT,
      file_num NUMERIC,
      sub_id NUMERIC NOT NULL
  );
  
  CREATE UNIQUE INDEX idx_cm_indiv ON cm_indiv (sub_id);
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>--insert joined table into new table
  INSERT INTO cm_indiv (
      cmte_id,
      cmte_pty_affiliation,
      contributor_name,
      contributor_employer,
      contributor_transaction_dt,
      cand_id,
      transaction_amt,
      file_num,
      sub_id
      )
  SELECT 
      cmte_id,
      cmte_pty_affiliation,
      name,
      employer,
      transaction_dt,
      cand_id,
      transaction_amt,
      file_num,
      sub_id
      FROM tmp;
  </code></pre>