Oracle 11g: Issue with INSERT ALL

[WolfShade]

Hello, all,

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
FROM old_table
</cfquery>
<cfquery name="new_data" datasource="#application.dsn_new#">
INSERT ALL
     <cfoutput query="old_data">
          into new_table(aCol, bCol, cCol)
               values(colA, colB, colC)
     </cfoutput>
SELECT * from dual;
</cfquery>

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?

V/r,

^ _ ^

[WolfShade]

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.

V/r,

^ _ ^

[EddieLotter]

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.

Cheers

Eddie

[WolfShade]

I actually revisited this during a break I was taking, and the solution turned out to be putting the values in CFQUERYPARAM tags.

V/r,

^ _ ^

PS:  Late or no, always good to hear your input.

[EddieLotter]

cfqueryparam is definitely the more robust option. Glad you got it sorted out.

Cheers

Eddie