前端之家

  1. 首页
  2. 问答

从Node.js向SQL Server插入多行

• 问答

我正在一个项目中，该项目将从node.js程序向SQL Server上传一些记录。现在，这是我的方法（在异步函数内部）：

{'__header__': b'MATLAB 5.0 MAT-file,Platform: x86_64-pc-linux-gnu,Created by: libmatio v1.5.9 on Mon Jul  6 20:29:51 2020','__version__': '1.0','__globals__': [],'diga': array([[(array([[(array([[0.,0.10464944,0.21018082,0.31937647,0.42176473,0.53570583,0.6428233,0.74994078,0.85705826,0.96417574,1.0681423,1.17389392,1.28139943,1.40048611,1.52559419,1.64743952,1.76928485,1.89113018,2.,2.12290829,2.24581658,2.36872488,2.49163317,2.59454399,2.69687044,2.81347033,2.93308651,3.05714129,3.17464839,3.3055228,3.45267061,3.58849168,3.77533445,3.88176795,3.998157,4.19674602,4.34787781,4.5,4.69631901,4.83781901,4.99708256,5.20507697,5.37073747,5.5,5.6080527,5.78533992,5.95166902,6.09878942,6.28218433,6.4540618,6.59066557,6.69357254,6.8539116,6.95566133,7.08274939,7.19399435,7.3374617,7.44876399,7.58496883,7.70381522,7.83578215,7.94780886,8.08803463,8.19780798,8.34935696,8.45694731,8.58460976,8.69666891,8.83509531,8.94704739,9.08658847,9.20783487,9.33469661,9.44355668,9.59016133,9.6962619,9.85349258,9.95928693]]),array([[88.3,88.30430713,88.30865056,88.3131448,88.31735886,88.32204841,88.32645712,88.33086583,88.33527453,88.33968324,88.34053444,88.33956722,88.33858396,88.33749478,88.33635052,88.3352361,88.33412169,88.33300727,88.33201153,88.33088739,88.32976326,88.32863912,88.32751498,88.32743846,88.32743846]]),array([[4.05436068,4.21002405,4.21327229,4.21486158,4.21591229,4.2168056,4.21748722,4.21807606,4.21860772,4.21910362,4.03324528,4.0265357,4.02467542,4.02350034,4.02273807,4.02225604,4.02191832,4.02166347,4.02147603,4.02129491,4.02113115,4.020979,4.02083454,4.05516985,4.05587307,4.05613862,4.05627884,4.05635271,4.05638517,4.0563976,4.0563957,4.05638637,4.05636811,4.05635644,4.05634327,4.05632056,4.05630343,4.05628649,4.05626516,4.05625018,4.05623371,4.05621284,4.0561967,4.05618439,4.0561743,4.05615809,4.05614328,4.05613048,4.0561149,4.05610068,4.05608961,4.05608141,4.05606887,4.05606105,4.05605142,4.05604313,4.05603261,4.05602458,4.05601491,4.0560066,4.05599751,4.05598991,4.05598053,4.0559733,4.05596346,4.05595658,4.05594852,4.05594153,4.05593303,4.05592624,4.05591789,4.05591073,4.05590333,4.05589706,4.05588872,4.05588277,4.05587405,4.05586825]]),array([[ 0.,-1.,0.,0. ]]),array([[25.,25.,25.]]),array([[0.00000000e+00,6.85992309e-01,7.03449743e-01,7.10867829e-01,7.15615588e-01,7.19174707e-01,7.21389575e-01,7.22912024e-01,7.24007563e-01,7.24847258e-01,4.45033989e-02,3.85461248e-02,3.58360767e-02,3.53935152e-02,3.56460808e-02,3.59131697e-02,3.60769536e-02,3.61500744e-02,3.61654153e-02,3.61491829e-02,3.61153062e-02,3.60739446e-02,3.60309650e-02,9.69429070e-04,5.35032457e-04,3.58732548e-04,2.92579042e-04,2.64021521e-04,2.49466440e-04,2.38203495e-04,2.27951943e-04,2.19594624e-04,2.09204995e-04,2.03745280e-04,1.98105959e-04,1.89197221e-04,1.82955847e-04,1.77092643e-04,1.70083570e-04,1.65383061e-04,1.60410350e-04,1.54374091e-04,1.49900760e-04,1.46597195e-04,1.43952210e-04,1.39825410e-04,1.36175399e-04,1.33110819e-04,1.29488217e-04,1.26275854e-04,1.23838901e-04,1.22066587e-04,1.19407152e-04,1.17780340e-04,1.15810875e-04,1.14141001e-04,1.12057839e-04,1.10493479e-04,1.08637272e-04,1.07067274e-04,1.05375319e-04,1.03979386e-04,1.02281688e-04,1.00989373e-04,9.92555314e-05,9.80583899e-05,9.66725802e-05,9.54858692e-05,9.40565860e-05,9.29290483e-05,9.15576202e-05,9.03953350e-05,8.92071874e-05,8.82095618e-05,8.68967397e-05,8.59677815e-05,8.46224613e-05,8.37375088e-05]]),8.37375088e-05]]))]],dtype=[('Programmdauer','O'),('SOCVec',('Spannung',('SpannungVec',('Strom',('StromVec',('TemperaturVec',('ThermischLeistung',('ThermischLeistungVec','O')]),)]],dtype=[('daten','O')])}

其中记录是以下形式的对象数组：

con = await sql.connect(`mssql://${SQL.user}:${SQL.password}@${SQL.server}/${SQL.database}?encrypt=true`);
for (r of RECORDS) {
        columns = `([column1],[column2],[column3])`;
        values = `(@col1,@col2,@col3)`;
        await con
            .request()
            .input("col1",sql.Int,r.col1)
            .input("col2",sql.VarChar,r.col2)
            .input("col3",r.col3)
            .query(`INSERT INTO [dbo].[table1] ${columns} VALUES ${values}`);
}

尽管如此，该代码仍然有效，我感觉它根本没有效率。我上载了大约4k记录，大约需要10分钟，看起来不太好。

我相信，如果我可以创建一个查询-而不是将单个插入内容包装在for循环中-使用所有记录值，它将更快，而且我知道在SQL中可以达到该语法：

RECORDS = [
    { col1: 1,col2: "asd",col3: "A" },{ col1: 2,col2: "qwerty",col3: "B" },// ...
];

但是，我无法从mssql模块中找到有关节点的文档，该文档涉及如何准备参数化的输入以在单个事务中完成所有操作。

有人可以引导我朝正确的方向前进吗？

谢谢。

iCMS 回答：从Node.js向SQL Server插入多行

此外，与批量插入非常相似，您可以使用表值参数。

sql.connect("mssql://${SQL.user}:${SQL.password}@${SQL.server}/${SQL.database}?encrypt=true")
  .then(() => {
    const table = new sql.Table();
    table.columns.add('col1',sql.Int);
    table.columns.add('col2',sql.VarChar(20));
    table.columns.add('col3',sql.VarChar(20));

    // add data
    table.rows.add(1,'asd','A');
    table.rows.add(2,'qwerty','B');

    const request = new sql.Request();
    request.input('table1',table);  

    request.execute('procMyProcedure',function (err,recordsets,returnValue) {  
       console.dir(JSON.stringify(recordsets[0][0]));  
       res.end(JSON.stringify(recordsets[0][0]));  
    });  
  });

然后在SQL端，创建用户定义的表类型

CREATE TYPE typeMyType AS TABLE
(
   Col1 int,Col2 varchar(20),Col3 varchar(20)
)

然后在存储过程中使用它

CREATE PROCEDURE procMyProcedure
   @table1 typeMyType READONLY
AS
BEGIN
   INSERT INTO table1 (Col1,Col2,Col3)
   SELECT Col1,Col3
   FROM @MyRecords
END

这使您可以更好地控制数据，并在实际插入之前对sql中的数据进行更多操作。

,

如@JoaquinAlvarez所指出的，应按此处的答复使用大容量插入：Bulk inserting with Node mssql package

对于我来说，代码就像：

return await sql.connect(`mssql://${SQL.user}:${SQL.password}@${SQL.server}/${SQL.database}?encrypt=true`).then(() => {
    table = new sql.Table("table1");
    table.create = true;
    table.columns.add("column1",sql.Int,{ nullable: false });
    table.columns.add("column2",sql.VarChar,{ length: Infinity,nullable: true });
    table.columns.add("column3",sql.VarChar(250),{ nullable: true });

    // add here rows to insert into the table
    for (r of RECORDS) {
        table.rows.add(r.col1,r.col2,r.col3);
    }

    return new sql.Request().bulk(table);
});

SQL数据类型必须（显然）匹配现有表table1的列类型。请注意column2的情况，这是SQL中定义为varchar（max）的列。

谢谢华金！我将时间从10分钟缩短到了几秒钟

node-mssqlnode.jssql-servertedious
本文链接：https://www.f2er.com/1901543.html

大家都在问