使用Node mssql包批量插入 [英] Bulk inserting with Node mssql package
问题描述
我正在尝试从csv文件中的MSSQL Server中同时插入大约20.000个用户.我为Angular2应用程序创建了一个API,并将csv文件解析为JSON格式.但是现在我有点卡住了,我已经从2岁多的年龄中找到了答案,所以它不再起作用了.有什么帮助吗?
这是我已经在使用的代码:
//Insert gebruikers from CSV file
router.post('/gebruikers/csv', type, (req, res) => {
fs.readFile(req.file.path, 'utf8', function (err, csvData) {
if (err) {
res.send("error in file reader: " + err);
return console.log(err);
}
csvParser(csvData, {
delimiter: ',',
columns: true
}, function (err, json) {
if (err) {
res.send("error in csvParser: " + err);
console.log(err);
} else {
console.log(json);
//I think the query should be done here...
}
})
})
});
提前谢谢!
编辑
现在使用此代码,但会返回此错误:
TypeError: parameter.value.getTime is not a function
代码:
router.post('/gebruikers/csv', type, (req, res) => {
fs.readFile(req.file.path, 'utf8', function (err, csvData) {
if (err) {
res.send("error in file reader: " + err);
return console.log(err);
}
csvParser(csvData, {
columns: true,
ltrim: true,
rtrim: true
}, function (err, json) {
if (err) {
res.send("error in csvParser: " + err);
console.log(err);
} else {
console.log(json);
const table = new sql.Table('[Alg].[User]');
table.create = true;
table.columns.add('Firstname', sql.NVarChar, { nullable: false });
table.columns.add('LastName', sql.NVarChar, { nullable: false });
table.columns.add('PrivateMail', sql.NVarChar, { nullable: false });
table.columns.add('UserName', sql.NVarChar, { nullable: false });
table.columns.add('Password', sql.NVarChar, { nullable: false });
table.columns.add('Auth', sql.NVarChar, { nullable: false });
table.columns.add('Enabled', sql.Bit, { nullable: false });
table.columns.add('Created', sql.SmallDateTime, { nullable: false });
table.columns.add('Manual', sql.Bit, { nullable: false });
table.columns.add('LastChanged', sql.SmallDateTime, { nullable: false });
table.columns.add('Staff', sql.Bit, { nullable: false });
var count = Object.keys(json).length;
console.log(count);
for (i = 0; i < count; i++) {
table.rows.add(json[i].Firstname, json[i].LastName, json[i].PrivateMail, json[i].UserName, json[i].Password, 'manual', 1, "GetDate()", 1, "GetDate()", 1);
}
console.log("Async function started!");
const request = new sql.Request();
request.bulk(table, (err, result) => {
// error checks?
if (err) {
console.log("ERROR post bulk gebruikers: " + err);
}
else {
res.send("SUCCES! " + result);
}
})
}
})
})
});
已修复
正如用户 Grigoriy Chudnov 所指出的,我需要new Date()
的实例.我当时认为这行不通,因为格式不正确,但是node-mssql会为我处理.
currentDateTime = new Date();
table.rows.add(json[i].Firstname, json[i].LastName, json[i].PrivateMail, json[i].UserName, json[i].Password, 'manual', 1, currentDateTime, 1, currentDateTime, 1);
如果您使用的是 node-mssql 库,请使用批量插入一次添加多个记录./p>
它应该看起来像这样:
'use strict';
const sql = require('mssql');
const user = 'sa';
const password = 'yourStrong(!)Password';
const connStr = `mssql://${user}:${password}@172.17.0.2:1433/tempdb?encrypt=true`;
sql.connect(connStr)
.then(() => {
console.log('connected');
const table = new sql.Table('my_users');
table.create = true;
table.columns.add('id', sql.Int, { nullable: false, primary: true });
table.columns.add('name', sql.VarChar(128), { nullable: false });
// add here rows to insert into the table
table.rows.add(1, 'Alice');
table.rows.add(2, 'Bob');
table.rows.add(3, 'Carol');
const request = new sql.Request();
return request.bulk(table)
})
.then(data => {
console.log(data);
})
.catch(err => {
console.log(err);
});
输出为:
connected
{ rowsAffected: 3 }
I'm trying to insert around 20.000 users at the same time in my MSSQL Server from a csv file. I created an API for my Angular2 application and I parsed the csv file to JSON format. But now I'm kind of stuck, I found an answer for this from more that 2 years old so that's not working anymore. Any help?
This is the code I'm already using:
//Insert gebruikers from CSV file
router.post('/gebruikers/csv', type, (req, res) => {
fs.readFile(req.file.path, 'utf8', function (err, csvData) {
if (err) {
res.send("error in file reader: " + err);
return console.log(err);
}
csvParser(csvData, {
delimiter: ',',
columns: true
}, function (err, json) {
if (err) {
res.send("error in csvParser: " + err);
console.log(err);
} else {
console.log(json);
//I think the query should be done here...
}
})
})
});
Thanks in advance!!
EDIT
Using this code right now but it returns this error:
TypeError: parameter.value.getTime is not a function
Code:
router.post('/gebruikers/csv', type, (req, res) => {
fs.readFile(req.file.path, 'utf8', function (err, csvData) {
if (err) {
res.send("error in file reader: " + err);
return console.log(err);
}
csvParser(csvData, {
columns: true,
ltrim: true,
rtrim: true
}, function (err, json) {
if (err) {
res.send("error in csvParser: " + err);
console.log(err);
} else {
console.log(json);
const table = new sql.Table('[Alg].[User]');
table.create = true;
table.columns.add('Firstname', sql.NVarChar, { nullable: false });
table.columns.add('LastName', sql.NVarChar, { nullable: false });
table.columns.add('PrivateMail', sql.NVarChar, { nullable: false });
table.columns.add('UserName', sql.NVarChar, { nullable: false });
table.columns.add('Password', sql.NVarChar, { nullable: false });
table.columns.add('Auth', sql.NVarChar, { nullable: false });
table.columns.add('Enabled', sql.Bit, { nullable: false });
table.columns.add('Created', sql.SmallDateTime, { nullable: false });
table.columns.add('Manual', sql.Bit, { nullable: false });
table.columns.add('LastChanged', sql.SmallDateTime, { nullable: false });
table.columns.add('Staff', sql.Bit, { nullable: false });
var count = Object.keys(json).length;
console.log(count);
for (i = 0; i < count; i++) {
table.rows.add(json[i].Firstname, json[i].LastName, json[i].PrivateMail, json[i].UserName, json[i].Password, 'manual', 1, "GetDate()", 1, "GetDate()", 1);
}
console.log("Async function started!");
const request = new sql.Request();
request.bulk(table, (err, result) => {
// error checks?
if (err) {
console.log("ERROR post bulk gebruikers: " + err);
}
else {
res.send("SUCCES! " + result);
}
})
}
})
})
});
FIXED
As user Grigoriy Chudnov pointed out, I need an instance of new Date()
. I was thinking this wouldn't work because it would be in the wrong format, but node-mssql handles this for me.
currentDateTime = new Date();
table.rows.add(json[i].Firstname, json[i].LastName, json[i].PrivateMail, json[i].UserName, json[i].Password, 'manual', 1, currentDateTime, 1, currentDateTime, 1);
If you're using node-mssql library, use bulk insert to add multiple records at once.
It should look something like this:
'use strict';
const sql = require('mssql');
const user = 'sa';
const password = 'yourStrong(!)Password';
const connStr = `mssql://${user}:${password}@172.17.0.2:1433/tempdb?encrypt=true`;
sql.connect(connStr)
.then(() => {
console.log('connected');
const table = new sql.Table('my_users');
table.create = true;
table.columns.add('id', sql.Int, { nullable: false, primary: true });
table.columns.add('name', sql.VarChar(128), { nullable: false });
// add here rows to insert into the table
table.rows.add(1, 'Alice');
table.rows.add(2, 'Bob');
table.rows.add(3, 'Carol');
const request = new sql.Request();
return request.bulk(table)
})
.then(data => {
console.log(data);
})
.catch(err => {
console.log(err);
});
And the output is:
connected
{ rowsAffected: 3 }
这篇关于使用Node mssql包批量插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!