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;
[code]
<cfquery datasource="mydb">
INSERT INTO mytable
(id, somefield)
SELECT '1', thefield
FROM demo
WHERE id = 1
</cfquery>
[/code]
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;
[code]
<cfquery datasource="mydb">
INSERT INTO mytable
(id, somefield, thedate)
SELECT '1', thefield, '#now()#'
FROM demo
WHERE id = 1
</cfquery>
[/code]
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;
[code]
<cfquery datasource="mydb">
INSERT INTO mytable
(id, somefield, thedate)
SELECT '1', thefield, systimestamp
FROM demo
WHERE id = 1
</cfquery>
[/code]
The “systimestamp” will insert the current date and time for you.
