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

Oracle 11g: Issue with INSERT ALL

LEGEND ,
Apr 19, 2018 Apr 19, 2018

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
898
Translate
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

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,

^ _ ^

Translate
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

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

Translate
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

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.

Translate
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
LATEST

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

Cheers

Eddie

Translate
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