Bulk insert data in mssql table using node mssql

Question

My query file is as below

BEGIN TRANSACTION
DECLARE @status NVARCHAR(30);
SET @status = 'create';
DECLARE @i UNIQUEIDENTIFIER;
SET @i = NEWID();
DECLARE @t DATETIME2;
SET @t = SYSUTCDATETIME();
IF NOT EXISTS( 
    SELECT * FROM user WHERE email = @email AND company_id= @company_id
) BEGIN
SET @i = NEWID();
INSERT INTO user (comapny_id, id, email, password) VALUES ( @company_id, @i, @email, @password);
INSERT INTO user_transaction( id, date, type) VALUES ( @i, @t, @status);
SELECT @i as 'id', @email as 'email';
END ELSE BEGIN
SELECT NULL as 'id', @email as 'email';
END
COMMIT TRANSACTION

And my createuserquery in query.js file is

 datastore.getQueryFromSqlFile('create_user', (err: any, query: string) => {
    if (err) {
        done(err);
    } else {
        var request = new sql.Request(connectionOrTransaction);
        request.input('email', sql.NVarChar(200), email);
        request.input('password', sql.NVarChar(200), some_password);
        request.input('company_id', sql.UniqueIdentifier, company_id);
        request.query(query, function (err, data) {});

Now I need to modify these to insert bulk of user data imported from CSV file (>20000 entries) I was thinking of doing something like

 async.mapSeries(Object.keys(users), function (item, callback) {
     query.createuser(email, company_id, function (err, data) {
         callback(err, err ? 'Error message: ' + data : data);        
     });
 }, function (err, results) {
 })

But this is not efficient as I get connection timeout. Increasing connectionTimeout or requestTimeout in config file doesn't help much.

How could I make my query faster for bulk insert around 20000-40000 entries in per attempt?

score 1 · Answer 1 · answered Oct 14 '14 at 08:41

For me it looks like a job for a prepared statement.

var ps = new sql.PreparedStatement();
ps.input('email', sql.VarChar);
ps.input('password', sql.VarChar);
ps.input('company_id', sql.Int);
ps.prepare(" ... your sql ... ", function(err) {
    // ... error checks

    // users must be an array of users
    async.mapSeries(users, function(user, next) {
        ps.execute({email: user.email, password: user.password, company_id: user.company_id}, next);
    }, function(err) {
        // ... error checks

        ps.unprepare(function(err) {
            // ... error checks

            // done !
        });
    });
});

Every execute is called as a single request, so you should not be timeouted by requestTimeout. connectionTimeout is something that only affect connecting phase. Once you're connected to db, only requestTimeout matters.

Bulk insert data in mssql table using node mssql

1 Answers1