Insert array into table

[fyrehed]

Hello,

I have a 2 dimensional array that i need to insert into table. Can someone point me in the right direction ?

Matt

[Avatar]

You cannot directly insert the Coldfusion array object into a database, you need to insert each Array Element seperately.

Example:

<cfset MyArray = ArrayNew(2)>
<cfset MyArray[1][1] = "MyFirstName"><!--- [1] Always firstname --->
<cfset MyArray[1][2] = "MyLastName"><!--- [2] Always lastname --->
<cfset MyArray[2][1] = "YourFirstName"><!--- [1] Always firstname --->
<cfset MyArray[2][2] = "YourLastName"><!--- [2] Always lastname --->


<cfloop index="x" from="1" to="#ArrayLen(MyArray)#">
<cfquery>
INSERT INTO aTable (FirstName, LastName)
VALUES ('#MyArray[1]#', '#MyArray[2]#')
</cfquery>
</cfloop>

[fyrehed · Accepted solution]

I have tried this....and get an error, with the naming of the variable in the cfset. Can anyone tell me what i am doing wrong?

<cfloop index="i" from="2" to="#arraylen(arrCSV)#" step="1">
<cfoutput>Record #i#</cfoutput><br />
<cfloop index="ii" from="1" to="126" step="1">
<cfif #arrcsv [ii]# IS "">
<cfoutput><cfset row#ii#=NULL></cfoutput>
<cfelse>
<cfoutput><cfset row#ii#=#arrCSV[ii]#></cfoutput>
</cfif>
</cfloop>
<cfoutput>
<cfquery datasource="redding" name="insertArray">
Insert INTO Gabriels_Upload(ListNum, AgencyName, AgencyPhone, ListingAgent, CoListingAgent, PropertyType, CardFormat, booksection, sellingagency, sellingagent, CoSellingAgent, EndDate, Type, listdate, SoldDate, UnderContractDate, FallThroughDate, Status, StatusChangeDate, WithdrawDate, cancelDate, contingent, ContingentRemarks, OriginalList, ListPrice, SoldPrice, HighPrice, LowPrice, AssessedVal, ManagerPhone, Financing, Area, LockBoxNBR, StreetNumber, POBox, StreetDIRPrefix, Streetname, No_Common_Name1, No_Common_Name2, StreetSuffix, CarrierRoute, City, State, County, Model, PostalCode, GEO_County, geo_Block, No_Common_Name3, longitude, ApproximateSQFT, No_Common_Name4, No_Common_Name5, No_Common_Name6, No_Common_Name7, YrBuilt, style, Realtor_com_type, LotDimORAcres, LotAcres, LotSize, No_Common_Name8, Spec_Comm_See_Rem,stories, total_Room, TotalBedrooms,totalBath, PricePerMonth, PricePerSQFT, Baths_3_4, Garage_type, Garage_stall, Garage_Rem, No_Common_Name9, Taxes, Tax_Yr, Subdivision, PublicRemarks, PrivateRemarks, ParcelNum, Legal, Directions, Owner, OwnerPhone, manager, rented, mod_timeStamp, No_Common_Name10, No_Common_Name11, No_Common_Name12, No_Common_Name13, ListingType, mapref, No_Common_Name14, crossStreet, Yr_Built_Description, No_Common_Name15, LockBoxLoc, ScopeOfService, userdefined13, userdefined14, userdefined15, userdefined16, userdefined17, userdefined18, userdefined19, userdefined20, userdefined21, userdefined22, userdefined23, userdefined24, userdefined25, userdefined26, userdefined27, userdefined28, userdefined29, Order_30, userdefined31, userdefined32, userdefined33, userdefined34, TourRemarks, PhotoURL, DaysonMarket, rooms, features, insertdate)
Values('#row1#', '#row2#', '#row3#', '#row4#', '#row5#', '#row6#', '#row7#', '#row8#', '#row9#', '#row10#', '#row11#', '#row12#', '#row13#', '#row14#', '#row15#', '#row16#', '#row17#', '#row18#', '#row19#', '#row20#', '#row21#', '#row22#', '#row23#', '#row24#', '#row25#', '#row26#', '#row27#', '#row28#', '#row29#', '#row30#', '#row31#', '#row32#', '#row33#', '#row34#', '#row35#', '#row36#', '#row37#', '#row38#', '#row39#', '#row40#', '#row41#', '#row42#', '#row43#', '#row44#', '#row45#', '#row46#', '#row47#', '#row48#', '#row49#', '#row50#', '#row51#', '#row52#', '#row53#', '#row54#', '#row55#', '#row56#', '#row57#', '#row58#', '#row59#', '#row60#', '#row61#', '#row62#', '#row63#', '#row64#', '#row65#', '#row66#', '#row67#', '#row68#', '#row69#', '#row70#', '#row71#', '#row72#', '#row73#', '#row74#', '#row75#', '#row76#', '#row77#', '#row78#', '#row79#', '#row80#', '#row81#', '#row82#', '#row83#', '#row84#', '#row85#', '#row86#', '#row87#', '#row88#', '#row89#', '#row90#', '#row91#', '#row92#', '#row93#', '#row94#', '#row95#', '#row96#', '#row97#', '#row98#', '#row99#', '#row100#', '#row101#', '#row102#', '#row103#', '#row104#', '#row105#', '#row106#', '#row107#', '#row108#', '#row109#', '#row110#', '#row111#', '#row112#', '#row113#', '#row114#', '#row115#', '#row116#', '#row117#', '#row118#', '#row119#', '#row120#','#row121#', '#row122#', '#row123#', '#row124#', '#row125#', '#row126#', '#tday#')
</cfquery>
</cfoutput>
</cfloop>

[fyrehed]

With the <cfset 'row#ii#'=#arrCSV [ii]#>


The above code works great!! Thanks for your help avatar!