Create a gist now

Instantly share code, notes, and snippets.

MySQL bulk Insert
/**
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();
@kekscom
Owner

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment