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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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.