Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
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
Something went wrong with that request. Please try again.