SixSigns Blog

To issue a “INSERT TO” with a “SELECT” statement is a common practice to copy records from one table to another. A statement like this might look like;

<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #800000">&lt;cfquery<span style="color: #000000"> </span>datasource=<span style="color: #0000ff">"mydb"</span>&gt;</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #000066">INSERT</span> <span style="color: #0000ff">INTO</span> mytable</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal">(id, somefield)</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">'1'</span>, thefield</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">FROM</span> demo</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">WHERE</span> id = 1</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #0000ff"><span class="Apple-style-span" style="color: #800000">&lt;/cfquery&gt;</span></p>

No big deal. Al right, now many times one also adds the current date to the table. Now as ColdFusion developers you might me inclined to write this code;

<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #800000">&lt;cfquery<span style="color: #000000"> </span>datasource=<span style="color: #0000ff">"mydb"</span>&gt;</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #000066">INSERT</span> <span style="color: #0000ff">INTO</span> mytable</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal">(id, somefield, thedate)</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">'1'</span>, thefield, '#now()#'</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">FROM</span> demo</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">WHERE</span> id = 1</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #0000ff"><span class="Apple-style-span" style="color: #800000">&lt;/cfquery&gt;</span></p>
 

But what a surprise the code above will not work since Oracle is not able to interpret the “#now()#’ field (yes, we are on a CFM page, but it still does not work).Simple solution is to use the Oracle timestamp with;

<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #800000">&lt;cfquery<span style="color: #000000"> </span>datasource=<span style="color: #0000ff">"mydb"</span>&gt;</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #000066">INSERT</span> <span style="color: #0000ff">INTO</span> mytable</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal">(id, somefield, thedate)</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">'1'</span>, thefield, systimestamp</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">FROM</span> demo</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal"><span style="color: #0000ff">WHERE</span> id = 1</p>
<p style="margin: 0px; font-family: Monaco; font-style: normal; font-variant: normal; font-weight: normal; font-size: 11px; line-height: normal; font-size-adjust: none; font-stretch: normal; color: #0000ff"><span class="Apple-style-span" style="color: #800000">&lt;/cfquery&gt;</span></p>
 

The “systimestamp” will insert the current date and time for you.

If you enjoyed this post, make sure you subscribe to the RSS feed!