• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Oracle 11g: Issue with INSERT ALL

LEGEND ,
Apr 19, 2018 Apr 19, 2018

Copy link to clipboard

Copied

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,

^ _ ^

TOPICS
Database access

Views

801

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 19, 2018 Apr 19, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 24, 2018 Apr 24, 2018

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 24, 2018 Apr 24, 2018

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 25, 2018 Apr 25, 2018

Copy link to clipboard

Copied

LATEST

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

Cheers

Eddie

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation