Read JSON from a REST API, turn that into SQL requests, execute the SQL request against a SQL Server DB.

app.js

var fs = require('fs');
var http = require('http');
var nodemailer = require('nodemailer');

var dbServer = '...';
var dbName = '...';
var dbUser = '...';
var dbpassword = '...';
var smtpHost = '...';
var mailFrom = '...';
var mailTo = '...';

var smtpTransport = nodemailer.createTransport('SMTP', {
  host: smtpHost
});

var mailOptions = {
  from: mailFrom, // sender address
  to: mailTo, // list of receivers
  subject: "MCMAINT-465 - Reconcile device ids following iOS upgrade", // Subject line
  text: ""
}

var fd;
var file = fs.createWriteStream('updates.sql', {
  flags: 'w',
  encoding: 'ascii'
});
file.on('open', function(fd_) {
  fd = fd_;
  //console.log('fd: ' + fd);
});
file.on('finish', function() {
  fs.close(fd, function(data) {
    //console.log('Error closing file: '+data);
  });

  console.log('Sending updates to SQL server');

  var child_process = require('child_process');
  var sqlcmd = child_process.spawn('sqlcmd', ['-S', dbServer '-d', dbName, '-U', dbUser, '-P', dbPassword, '-i', 'updates.sql']);

  sqlcmd.stdout.on('data', function(data) {
    //console.log('stdout: ' + data);
  });

  sqlcmd.stderr.on('data', function(data) {
    //console.log('stderr: ' + data);
  });

  sqlcmd.on('exit', function(code) {
    console.log('Child process exited with exit code ' + code);
    mailOptions.text = 'Child process exited with exit code ' + code + '\n\n' + mailOptions.text;

    // send mail with defined transport object
    smtpTransport.sendMail(mailOptions, function(error, response) {
      if (error) {
        console.log(error);
      } else {
        console.log("Message sent: " + response.message);
      }
      smtpTransport.close(); // shut down the connection pool, no more messages
    });
  });
});

var data = "";
var options = {
  hostname: '10.160.211.110',
  path: '/v1/devices/list'
};
var req = http.request(options, function(res) {
  res.on('data', function(chunk) {
    //console.log("some data");
    data += chunk;
  });
  res.on('end', function() {
    var json = JSON.parse(data);

    for (var list in json) {
      var devices = json[list];
      for (var i = 0; i < devices.length; i++) {
        var device = devices[i];
        if (device["uid"]) {
          var update = "UPDATE panel.device SET hardware_id = '" + device["devid"] + "' WHERE uid = '" + device["uid"] + "' AND project = 4;";
          mailOptions.text += update + '\n';
          //console.log(update);
          file.write(update);
          file.write('\n');
        }
      }
    }

    file.write("GO");
    file.end();
  });
});

req.on('error', function(e) {
  console.log("Got error: " + e.message);
});

req.end();