Node.js mssql bulk insert into temp table

Question

0.00/5 (No votes)

See more:

I'm using node-mssql to connect to sql server 2012 with node.js. I've discovered the bulk insert in the doc[^] and I want to use it.

I want to insert data into a temp table, which the doc says I can do. I can't find any example of actually doing that though. Here's what I'm trying:

JavaScript

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);
            });
        });

here's the output I get :

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

If I remove the hash then it creates a real table on the server. I don't want a permanent table, I just need the temp table. If I can't do it with a temp table, then I'll have to take another approach.

Anyone know how this works ?

Posted 15-Apr-15 5:24am

loctrice

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

loctrice · Answer 1 · 2015-04-15T08:39:00

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:

JavaScript

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.

Node.js mssql bulk insert into temp table

1 solution

Solution 1

Add your solution here

Preview 0

Node.js mssql bulk insert into temp table

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us