title: Exporting big data to CSV files
body: <p>My current task requires me to export an estimated 100,000 rows of data from a very large database.</p>
  
  <p>I'm fairly new to handing big data and I would love to hear a few best practices and guidelines from people who have <strong>had prior experiences</strong> with these issues that <strong>worked for them in the past</strong> in an effort to make this post <strong>non-subjective</strong>.</p>
  
  <p>A few more details:</p>
  
  <ul>
  <li><p>The database is not at all normalized (very ugly)</p></li>
  <li><p>I have to deal with at least 100,000 rows in total</p></li>
  <li><p>The task is run at midnight with less users</p></li>
  <li><p>Currently using ColdFusion 9, PostgreSQL 8.4</p></li>
  </ul>
  
  <p>Thank you!</p>
  
  <p>This is what my code looks like after applying Craig's solution:</p>
  
  <pre><code>&lt;cfset base_path = GetDirectoryFromPath(ExpandPath("*.*")) &amp; "some_parent\some_child\"&gt;
  
  &lt;cfif not DirectoryExists(base_path)&gt;
      &lt;cfdirectory directory="#base_path#" action="create" mode="777"&gt;
  &lt;/cfif&gt;
  
  &lt;cfset this_batch_path = DateFormat(Now(), 'mmddyyyy') &amp; TimeFormat(Now(), 'hhmmss') &amp; "\"&gt;
  &lt;cfdirectory directory="#base_path##this_batch_path#" action="create" mode="777"&gt;
  
  &lt;cfset this_filename = "someprefix_" &amp; DateFormat(Now(), 'yyyymmdd') &amp; ".csv"&gt;
  &lt;cffile action="write" file="#base_path##this_batch_path##this_filename#" output=""&gt;
  
  &lt;cfset escaped_copy_path = ListChangeDelims(base_path &amp; this_batch_path &amp; this_filename, "\\", "\")&gt;
  
  &lt;cfquery name="qMyQuery" datasource="some_db" username="some_uname" password="some_pword" result="something"&gt;
      COPY some_table TO '#escaped_copy_path#' WITH CSV HEADER;
  &lt;/cfquery&gt;
  </code></pre>
  
  <p>Now I need to get the count of copied rows.
  In the PGSQL 8.4 docs:</p>
  
  <blockquote>
    <p>Outputs</p>
    
    <p>On successful completion, a COPY command returns a command tag of the
    form</p>
    
    <p>COPY count</p>
    
    <p>The count is the number of rows copied.</p>
  </blockquote>
  
  <p>But I can't seem to get it to work, even with the result tag and the query itself. </p>