Multiple inserts at a time

Question

I have requirement to insert around 2500-3000 rows of data inside a table at a time. I need to do this for both MySQL and SQL Server. So the possible solutions I found is

1. Writing cfquery inside cfloop.
2. Using cfloop inside cfquery to generate sql statement like INSERT INTO tbl VALUES((),(),()) . But the problem with this syntax is it can only insert 1000 records at a time not more than that. I need more.

Can anyone suggest me Whats the best way to handle this kind of situations?

Answer 1

When you are dealing with thousands of records, small incremental delays start adding up quickly. Inserting this number of rows one by one will take considerably more time than issuing a BULK INSERT statement for several reasons like increased network traffic, internal database actions, etc.

I am going to assume that you are receiving your data in a file. In that case, these are the general steps you would need to take:

1. Validate the data. VERY IMPORTANT. Even if you are receiving data from a trusted source in a trusted environment, files can easily be corrupted, truncated, or damaged in many ways. They can also contain discrepancies due to the small but sometimes significant differences between server versions (SQL Server 2000 vs 2008) or vendors (your source might be an Oracle database).
2. Issue a BULK INSERT statement, assuming you are using SQL Server 2005 or newer. I think there are some differences in the way the command works between 2005 and newer versions, so check the syntax before trying it.
3. Depending on your configuration and anything else you might need, you may want to update the index(es) on the table, or even recreate them. Again, this depends on how big the table is and your needs. This part of the process will potentially take the longest and may even take the table offline but your users will be very grateful that you did it.

It pays to plan the whole insertion process carefully so that you do not disrupt normal business activities and continue to provide the best service and availability.

As a closing thought, you should run tests using different methods. One-by-one vs bulk to make sure you are getting the best performance. I believe, however, that you'll always find that batches or bulk insertions are the way to go. The reason they added that capability into the Transact-SQL language is performance. This will save network bandwidth, resources, and time.

Answer 2

In SQL Server (at least 2008 and above) you can insert multiple rows at one time through a single INSERT statement:

INSERT INTO mytable (column1, column2) VALUES (value1, value2), (value3, value4);

I don't know if there is a limit (such as 1000) on the number of rows that can be inserted at once, but I agree with @RhysJones that you might want to limit to, say, 100 rows at a time in case an error is encountered - this will make it much easier to spot the error and correct it.

Since you're using ColdFusion, my recommendation would be to loop over the rows of the external file 100 at a time:

 <cfquery name="q_insert_rows">
    INSERT INTO mytable ( column1, column2, column3 )
    VALUES
    <cfloop query="external_data" startrow="#startrow#" endrow="#endrow#">
        ( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#value1#" />
        , <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#value2#" />
        , <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#value3#" /> )
        <cfif currentRow NEQ endrow> , </cfif>
    </cfloop>
 </cfquery>

The <cfquery> block above should be put inside a loop:

<cfloop from="1" to="#external_data.recordCount#" step="100" index="startrow">
    <cfset endrow = min(startrow + 99, external_data.recordCount) />
    ...
</cfloop>

If you want the whole thing to fail if one insert fails, then wrap the above loop in <cftransaction>.

Answer 3

You've tagged this with MySQL, so I'm presuming that you're using MySQL. I've had experience with this sort of issue as well, where we were importing our HR information from an external database. I found that it was significantly faster to validate the data, store it in a CSV file, and import that using the LOAD DATA INFILE syntax.

Answer 4

If possible, use a more appropriate tool to solve this problem instead of hammering away at it with ColdFusion.

SQL Server has the ability to directly import the contents of a spreadsheet.

Once connected to Sql Server 2005 Database, From Object Explorer Window, right click on the database which you want to import table into. Select Tasks -> Import Data. This is a simple tool and allows you to 'map' the incoming data into appropriate table. You can save the scripts to run again when needed.

You should be able to create a function in SQL Server that you can call from ColdFusion, passing in the location of the spreadsheet on your server (in a non-web-accessible folder, of course).

For MySQL, as Paul Rowe already stated, use the LOAD DATA INFILE syntax. Another SO answer suggests using the Windows based HeidiSQL to craft the statement via a UI to get the correct mappings.

Answer 5

Just don't use the multi-values version of INSERT. Use 1 INSERT statement per row, i.e.

INSERT INTO tbl VALUES(...);
INSERT INTO tbl VALUES(...);
INSERT INTO tbl VALUES(...);