I'm trying to migrate data from an old schema to a new schema via ColdFusion, and I'm getting an error message.
<cfquery name="old_data" datasource="#application.dsn_old#">
SELECT colA, colB, colC
<cfquery name="new_data" datasource="#application.dsn_new#">
into new_table(aCol, bCol, cCol)
values(colA, colB, colC)
SELECT * from dual;
904: ORA-00904: "colC": Invalid identifier
If I CFDUMP the first query, all the data is there. Why does Oracle think that colC is invalid?
^ _ ^
Never mind. Gave up the ghost. Too much time spent on it, and I need to get stuff done. I just bit the bullet and did individual CFQUERY within the CFOUTPUT, cringing all the way. It worked.
^ _ ^
Sorry I'm late to the party, but surely line 9 should be:
values(#colA#, #colB#, #colC#)
If any of them are not numeric, remember to include quotes.
I actually revisited this during a break I was taking, and the solution turned out to be putting the values in CFQUERYPARAM tags.
^ _ ^
PS: Late or no, always good to hear your input.
cfqueryparam is definitely the more robust option. Glad you got it sorted out.