Trying to load a JavaScript array into a SQL Server table. I convert the array to a string, however, its format does not immediately translate into something (I know of) to use as separate records to insert. So, I'm trying to convert the JavaScript array, to a string, and then that string into a multiple row, SQL values list.

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."],
]
strArray = JSON.stringify(jsArray)
console.log(strArray)

JavaScript string result:

[
    [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."]
]

Desired SQL value list rows:

INSERT INTO [MYTABLE] (
    [FIELD1], [FIELD1], [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.')
;

As you can see for SQL, each array needs to be surrounded by parathesis, each text field needs single quotes, etc.

I've been using a blunt force approach of a number of "replace" steps, tried for loops, some RegEx, but each seemed the wrong or an over complicated method, or couldn't quite get it right, or screwed up a valid instance of an entered special character (e.g. a double quote that needs to stay as a double quote).

Any ideas?

Trying to load a JavaScript array into a SQL Server table. I convert the array to a string, however, its format does not immediately translate into something (I know of) to use as separate records to insert. So, I'm trying to convert the JavaScript array, to a string, and then that string into a multiple row, SQL values list.

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."],
]
strArray = JSON.stringify(jsArray)
console.log(strArray)

JavaScript string result:

[
    [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."]
]

Desired SQL value list rows:

INSERT INTO [MYTABLE] (
    [FIELD1], [FIELD1], [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.')
;

As you can see for SQL, each array needs to be surrounded by parathesis, each text field needs single quotes, etc.

I've been using a blunt force approach of a number of "replace" steps, tried for loops, some RegEx, but each seemed the wrong or an over complicated method, or couldn't quite get it right, or screwed up a valid instance of an entered special character (e.g. a double quote that needs to stay as a double quote).

Any ideas?

• javascript
• sql
• arrays
• sql-server
• sql-insert

• 6 Mistake #1: not using proper SQL parameterization. Where is this JS running from, is it nodejs? Which DBMS is this, SQL Server, Postgres, something else? Please tag correctly, please show relevant code. – Charlieface Commented Mar 4 at 2:02
• Use a library that handles query parameterization. – Salman Arshad Commented Mar 4 at 5:38

1 Answer 1

You can try breaking it up to multiple operations and then bring the values together again. This approach can likely be optimized but, the approach would be to emphasize that the operation(s) can be broken up and then pieced together again.

Hope this helps or at least get you headed in the right direction.

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."],
];

const insertTo = (tableName) => `INSERT INTO [${tableName}](`;

const columnsTb = (colNum) =>
  Array.from(Array(colNum), (_, c) => {
    return `[FIELD${c + 1}]`;
  });

const rows = jsArray.map((r) => {
  return "('" + r.join("','") + "')";
});

console.log(
  insertTo("MYTABLE") + columnsTb(jsArray.length) + ")" + "VALUES" + rows
);