How to Create a Convert JavaScript Array to SQL Multirow Values List

Inserting data from a JavaScript array into a SQL Server table seems straightforward until you encounter special characters like quotes, commas, or brackets. Converting arrays into a SQL-compatible VALUES list requires careful handling to avoid syntax errors or data corruption. In this article, we’ll break down a robust method to transform JavaScript arrays into SQL-ready multi-row values while preserving data integrity. We’ll also explore additional enhancements to make the solution more versatile.

The Problem

When converting a JavaScript array to a SQL VALUES list, you need to:

1. Wrap strings in single quotes ('value').
2. Escape single quotes by doubling them ('').
3. Handle special characters like commas or brackets without breaking SQL syntax.
4. Maintain the structure of the original data.

Here’s the sample JavaScript array:

const jsArray = [
  [1, 'Cake', 'Special characters comma, left bracket ], right bracket ].'],
  [2, 'Ice cream', 'Single \'quote\' with "double" quote.'],
  [3, "Fudge Syrup", "Double \"quote\" with 'single' quote."]
];

The goal is to generate this SQL output:

INSERT INTO [MYTABLE] ([FIELD1], [FIELD2], [FIELD3])
VALUES
  (1, 'Cake', 'Special characters comma, left bracket ], right bracket ].'),
  (2, 'Ice cream', 'Single ''quote'' with "double" quote.'),
  (3, 'Fudge Syrup', 'Double "quote" with ''single'' quote.');

The Solution

Instead of manipulating the entire JSON string (which risks corrupting data), we’ll process each value individually.

Escape Single Quotes

SQL requires single quotes within strings to be escaped as ''. We’ll write a helper function to handle this:

function processValue(value) {
  if (value === null || value === undefined) return 'NULL'; // Handle nulls
  if (typeof value === 'string') {
    const escaped = value.replace(/'/g, "''"); // Escape single quotes
    return `'${escaped}'`;
  }
  return value; // Numbers or booleans
}

Convert Rows to SQL Syntax

Map each array row to a SQL-compatible string:

function convertToSQLValues(jsArray) {
  return jsArray.map(row => {
    const processedRow = row.map(processValue).join(', ');
    return `(${processedRow})`;
  }).join(',\n    ');
}

Generate the Full INSERT Statement

Combine everything into a complete SQL query:

function generateInsertStatement(table, fields, jsArray) {
  const values = convertToSQLValues(jsArray);
  return `INSERT INTO [${table}] (${fields.map(f => `[${f}]`).join(', ')})\nVALUES\n    ${values};`;
}

// Usage:
const sql = generateInsertStatement('MYTABLE', ['FIELD1', 'FIELD2', 'FIELD3'], jsArray);
console.log(sql);

Handling Edge Cases

• Null Values: The processValue function already returns NULL for null or undefined values.
• Dates: Convert Date objects to SQL-friendly strings:

if (value instanceof Date) {
  return `'${value.toISOString().slice(0, 19).replace('T', ' ')}'`; // YYYY-MM-DD HH:MM:SS
}

Booleans: Convert true/false to 1/0 for SQL Server:

if (typeof value === 'boolean') return value ? 1 : 0;

Enhanced Functionality

Batch Insertion Limits

SQL Server restricts batches to ~1000 rows. Split large arrays into chunks:

function chunkArray(arr, size) {
  return arr.length > size ? [arr.slice(0, size), ...chunkArray(arr.slice(size), size)] : [arr];
}

const batches = chunkArray(jsArray, 1000);
batches.forEach(batch => generateInsertStatement('MYTABLE', ['FIELD1', 'FIELD2', 'FIELD3'], batch));

Parameterized Queries (Safer Alternative)

Avoid SQL injection by using parameterized queries with libraries like mssql:

const { Request } = require('mssql');

async function bulkInsert(pool, table, rows) {
  const request = new Request(pool);
  rows.forEach((row, idx) => {
    request.input(`field1_${idx}`, row[0]);
    request.input(`field2_${idx}`, row[1]);
    request.input(`field3_${idx}`, row[2]);
  });
  await request.query(`INSERT INTO ${table} VALUES ${rows.map((_, i) => `(@field1_${i}, @field2_${i}, @field3_${i})`).join(', ')}`);
}

Final Thoughts

Manually converting JavaScript arrays to SQL VALUES lists works for small datasets, but always prioritize parameterized queries or ORM tools (like Sequelize) for security and scalability. If you must generate raw SQL, ensure thorough testing for edge cases like Unicode characters or emojis. The key takeaway: process each value individually instead of relying on regex hacks, and always escape user-generated content!