How To Make Batch Insert With Coldfusion Having More Than 1000 Records?

I am having a spreadsheet which contains around 3000 records. I need to insert all these data to a new table. So in this case using batch insert mechanism is quite good. So i tried

Solution 1:

You can use a BULK INSERT statement that should cope with extremely large datasets.

The data will need to be in a CSV, and you'll have to create a variable to the file location.

<cfquery datasource="cse">
    BULK INSERT Names
    FROM'#variables.sCSVLocation#'</cfquery>

If you have a reason not to use BULK INSERT and want to break it down into loops of 999, then you would have to work out how many 'records' are in the dataset, divide it by 999 to get the amount of times you'd have to loop over it.

Solution 2:

<cfquerydatasource="cse"><cfloopfrom="1"to="3000"index="i"><cfif ((iMOD1000) EQ1)><!--- Each SQL INSERT Can Only Handle 1000 Rows Of Data --->
    INSERT INTO Names
    (
    [colName]
    )
    VALUES
    </cfif>
    (
        '#i#'
    )
    <cfif (iLT3000)><cfif ((iMOD1000) NEQ0)>,</cfif>#CHR(13)##CHR(10)#</cfif></cfloop></cfquery>