nodejs mmsql bulk insert to temp table

Question

I'm using mssql to connect to sql server 2012 with node.js. I've discovered the bulk insert option in the docs

I'm wanting to insert data into a temporary table, which the doc says I can do. Here is what I'm trying:

createConnection(config.dbConfig);
    var table = new sql.Table('#atable');
    table.create = true;
    table.columns.add('a', sql.Int, { nullable: false});
    table.rows.add(1);
    table.rows.add(2);
    table.rows.add(3);
    var request = new sql.Request();
    request.bulk(table, function(err, rowCount){
       if(err){
           console.log('bulk insert error');
           console.log(err);
       } 
        var taco = new sql.Request();
        taco.query('select * from #atable', function(err, recordset){
           if(err){
               console.log('taco error:' + err);
           } 
           console.log('taco recordset:');
           console.log(recordset);
        });
    });

I get this output:

taco error:RequestError: Invalid object name '#atable'.

If I remove the hash then it creates a real table on the server. I can't find any examples actually using a temporary table. I'd like to know how to do this. If not, then I'll have to take another approach

score 2 · Accepted Answer · answered Apr 15 '15 at 18:38

I switched over to a regular sql statement to create a temporary table, and then tried to query it. What I realized was the new sql.Request was most of my problem. The temporary table doesn't exist on another connection/request/thread.

So this was my solution:

var table = new sql.Table('#atable');
    table.create = true;
    table.columns.add('a', sql.Int, { nullable: false});
    table.rows.add(1);
    table.rows.add(2);
    table.rows.add(3);
    var request = new sql.Request();
    request.bulk(table, function(err, rowCount){
       if(err){
           console.log('bulk insert error');
           console.log(err);
           return;
       } 

        request.query('select * from #atable', function(err, recordset){
       if(err){
           console.log('taco error:' + err);
           return;
       } 
       console.log('taco recordset:');
       console.log(recordset);
    });
    });

It's a subtle change. Inside the request.bulk call I used the same request object (smartly named request) to query the table. I wasn't creating a new connection object so I didn't realize I was using a connection where the table didn't exist.

nodejs mmsql bulk insert to temp table

1 Answers1

Linked