public
Last active

MySQL bulk Insert

  • Download Gist
BulkInsert.js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
/**
This little helper creates bulk SQL insert queries like this:
 
INSERT INTO mytable (id, col1, col2) VALUES
(1, "col1value", "col2value"),
(2, "col1value", "col2value"),
(3, "col1value", "col2value"),
:
:
(4999, "col1value", "col2value"),
(5000, "col1value", "col2value");
 
You just specify the limit and add keep adding rows.
 
 
* @param handle {object} reference to either a node mysql object or to a writable stream
* @param queryTemplate {string} a template, defining how your query should be populated, pass column names and insert options here
* @param limit {integer} a limit after how many queued items a query should be fired
* @param callback {function} gets passed to the sql query
*/
var BulkInsert = function(handle, queryTemplate, limit, callback) {
 
var queue = [];
 
/**
* Add data to the queue
*
* @method BulkInsert.add
* @param record {array} a list of items to insert as row
*/
this.add = function (record) {
queue.push(record.join(','));
if (queue.length > limit) {
this.flush()
}
}
/**
* Either execute the query or write to the file stream
*
* @method BulkInsert.flush
*/
this.flush = function () {
var sql = queryTemplate.replace('{values}', '\n('+ queue.join('),\n(') +')');
if (handle.path && handle.writable) {
handle.write(sql);
} else {
handle.query(sql, callback);
}
queue = [];
}
};
 
 
// preparation for executing MySQL queries
var handle = require('mysql').createClient({ user: '...', password: '...' });
 
// alternative: preparation for a file stream - aka MySQL dump
var handle = fs.createWriteStream('dump.sql');
 
// initialization
var myInserter = new BulkInsert(handle, 'INSERT INTO mytable (id, col1, col2) VALUES {values}', 5000, function () { ... });
 
// automatically creates ~200 bulk queries
for (var i = 1, il = 1000000; i < il; i++) {
myInserter.add([ i, 'col1value', 'col2value' ]);
}
 
// writes / executes the remaining items
myInserter.flush();

This little helper creates bulk SQL insert queries like this:

INSERT INTO mytable (id, col1, col2) VALUES
(1, "col1value", "col2value"),
(2, "col1value", "col2value"),
(3, "col1value", "col2value"),
:
:
(4999, "col1value", "col2value"),
(5000, "col1value", "col2value");

You just specify the limit and add keep adding rows.

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.