ColdFusion BulkInsert

A CFC that allows you to use MSSQL's BULK INSERT by simply following simple naming conventions and an existing XML Format File which can be created manually or by using the bcp Utility

A special thanks to Gert Franz (www.rasia.ch) as this CFC was inspired by his "How to make CFML script fast" session at Into The Box 2017. He also went above and beyond to review initial parts of the code base and help further optimize and teach me some additional things. If you want help optimizing your code, he is the man to call!

Example Usage

bulkInsert = new BulkInsert();

// minimum required arguments
bulkInsert.process(
    formatPath  : expandPath("./formats/"),
    dataPath    : expandPath("./temp/"),
    table       : "my_table",
    orderby     : "my_id",
    datasource  : "thesource"
);

Naming Conventions

The only requirement is that the name of the XML Format file is the same as the table you are working with. So if your table is called users, then the script is going to look for a file called users.xml within the formatPath you pass.

Arguments

• formatPath
  The absolute path to the folder where the format files exist
• dataPath
  The absolute path to the folder where the data files will be generated
• table
  The SQL table we are working with, the files used this as the name convention as well
• orderby
  The order used in the query required for paging
• datasource
  The datasource to pull records from
• iterator
  The number of records to work with at a time, defaults to 100k
• doCleanup
  A boolean value to clean up after itself when complete (delete generated file)
• replaceSQL
  Boolean value to run string replacements on SQL vs ColdFusion
• singleFieldSQL
  Boolean value to return all data in SQL as 1 field, if set to true replaceSQL is ignored
• debug
  A boolean value to return debug data
• debugOnly
  A boolean value to only debug (skips data pull and insert)
• doInsert
  A boolean value to run the final BULK INSERT statement. Set to false to only generate the file
• total
  A numeric value ot total records to process, this is handled internally but if a recordcount is requested outside of this or want to force a total you can pass it in