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
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.