Stream large table from to another table using NodeJS

HItesh Rana
HItesh Rana used Ask the Experts™
on
I'm trying to copy from table from one table to another. So i want to stream the large data in and as it comes in I want to copy the contents of that data to another table. Its more then just a straight copy. I will probably massage the data a bit and then copy it over to the new table. The problem that I'm having is that row event keeps firing and I can't control it. I tried using async and await where I was hoping to make it stop but does not seem to be working. I can't control an event from firing or not. This is what I have so far. So basically I want to simulate a cursor in MSSQL. I don't want to download all the data and then do operations on it.

I'm not showing the massaging of the data below since I just want to see if I can copy data from one table to another.

var fs = require('fs');
var sql = require('mssql');
var sql2 = require('mssql');
var Request = require('tedious').Request; 
var TYPES = require('tedious').TYPES;  
var SqlString = require('sqlstring');

const config = {
    user: 'xxxx',
    password: 'xxx',
    server: 'xxxx\\',
    port: 'xxxx',
    database: 'xxxx',
    options: {
        instanceName: 'sql2014'
    }
};


function insertRecord(pool, record){
    return new Promise(function(resolve, reject){

        const request = pool.request();

        request.input('invoice_id', sql.Int, record.invoice_id);

        request.query('INSERT INTO _invoice_temp(invoice_id)  VALUES(@invoice_id)', (err, result) => {
            if(err){
                reject(err);
            }

            resolve(1);
        });
     });
}

async function main() {
    try
    {
        let pool = (await sql.connect(config));
        const request = pool.request();
        request.stream = true;
        request.query('select * from invoice');

        var data = await request.on('row', row => {
                return new Promise(function(resolve, reject){
                    resolve(row);
                });
            });

        await insertRecord(pool, data);

    }
    catch(err){
        console.log(err);  
    }
};

main();

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are you wanting to do this in code or using SQL - the most logical approach is to create a trigger on the database that acts on the insert and then does the copy for you - that is the way I would do it.

Author

Commented:
I wanted to do this in NodeJS.  I know this can be implement in the database.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Then why not just do
INSERT INTO targetTable (field1, field2, ...) 
SELECT field1, field2, ... FROM sourceTable

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial