I'm using MSSQL bulk function to insert data into the database. The column customer_id does exist in the table.

When I debug and see the values that's in table.rows.push(map), I can see property customer_id and it has a integer value. I also debugged await request.bulk(table) and I can see property customer_id and it has a integer value.

I don't know why I'm getting the following error message.

Cannot insert the value NULL into column 'customer_id', table 'api.dbo.givers'; column does not allow nulls. INSERT fails."

try {
  let results = [];
  var response = fs
    .createReadStream('C:\\data\\givers.csv')
    .pipe(parse({ delimiter: ',', from_line: 2 }))
    .on('data', function (row) {
      results.push(row);
    });
  return new Promise(function (resolve, reject) {
    response.on('end', async () => {
      let pool = await sql.connect(connectionConfig);
      const table = new sql.Table('givers');
      table.create = false;
      table.columns.add('customer_id', sql.Int, { nullable: false });
      table.columns.add('source_giver_id', sql.VarChar, { nullable: false });
      ...

      results.forEach((row) => {
        const map = [];
        map['customer_id'] = customer_id;
        map['last_updated'] = moment(new Date()).format('YYYY-MM-DD HH:mm:ss');
        ...
        table.rows.push(map);
      });

      const request = new sql.Request(pool);
      await request.bulk(table); //error get's thrown here

      resolve(results.length);
    });
    response.on('error', reject);
  });
} catch (err) {
  if (err) return err;
}

CodePudding user response：

I was able to replicate this issue. I've been playing around and when I change const map = []; to const map = {}; it worked. I don't know why const map = []; didn't work because table.rows is an interface of interface Array<T> extends RelativeIndexable<T> {}

CodePudding user response：

I couldn't see where the customer_id field is coming from in the foreach. You should read the customer_id in the row

results.forEach((row) => {
  const map = [];
  map["customer_id"] = row.customer_id;
  table.rows.push(map);
});

I think it will work if you use it this way, because customer_id data is not available so it will be null.