0

I have a question about insert method in Oracle. I'm creating list in cold fusion that holds number of ID's that I have to insert into my table. All of these ID's will be inserted in the table with one more value that will be the same for all of them. So my question is do I have to use cfloop since I'm using coldfusion for this or I can simply just use pound symbols to output my list values in my insert method? I'm not able to test this now and I would like to make sure before I run my insert method that everything is ok. Here is my code:

<cfset myList = "57,688,456,2,55,88,98,144,77">

<cfquery name="InsertRec" database="test">
   Insert Into TEST_TABLE (ID, STATUS)
   Values('#myList#','Busy')
</cfquery>

Or can I use something like this:

<cfquery name="InsertRec" database="test">
    Insert Into TEST_TABLE (ID, STATUS)
    Values
    <cfloop list="#myList#" index="a">
        (
        <cfqueryparam cf_sql_type="cf_sql_integer" value="#a#">,
        'Busy'
        )
    </cfloop>
</cfquery>

I'm not sure if Oracle can handle insert for multiple rows this way or I have to use cfloop inside of my cfquery. If anyone knows what would be the best and most efficient in Oracle please let me know. Thank you.

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • Assuming the id's in `myList` are from another table, the simpler option is to use [INSERT / SELECT](http://stackoverflow.com/questions/19591652/coldfusion-how-to-insert-a-list-with-a-static-foreign-key-in-one-insert/19592264#19592264). Voting to close as this seems like a duplicate question. – Leigh May 25 '16 at 13:18

1 Answers1

0

Try this.

insert into test_table
(id, status)
select 0, ''
from dual
where 1 = 2
<cfloop list = "#myList#" index = "listItem">

union
select <cfqueryparam cfsqltype="cf_sql_integer" value="#listItem#">
, 'Busy'
from dual
</cfloop>

Of course, depending on where that list came from, there might be better ways.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43