Need Help with Bulk Insert Using JavaScript in SQL Mode

[Hayoung_Lee]

• Goal:
  Hello Retool Community,
  I am currently facing a challenge with implementing a bulk insert operation in SQL mode using JavaScript within Retool. My primary issue is that the result of my JavaScript expression is being treated as a string, which is causing syntax errors in the SQL query.

  Here is the scenario: I am trying to perform a bulk insert into a MySQL database.
  My table, FinancialTransactionLink , requires the following columns to be populated: InsPayId , RecvId , AmountCovered , and Notes .
  The data for InsPayId and RecvId is sourced from TableTFLMatch.data , while AmountCovered and Notes are obtained from TableTFLMatch.changesetArray .

• Steps:
  The problem arises when I try to concatenate these values into a SQL query using a JavaScript map function within the {{ }} notation. The JavaScript expression correctly creates the values string, but when inserted into the SQL query, it is treated as a single string (literal), leading to syntax errors.

• Details:

  INSERT INTO FinancialTransactionLink (InsPayId, RecvId, AmountCovered, Notes)
  VALUES 
  {{ 
      TableTFLMatch.changesetArray.map(change => {
          let row = TableTFLMatch.data.find(dataRow => dataRow.RecvId === change.RecvId);
          return '(' + row.InsPayId + ', ' + row.RecvId + ', ' + change.AmountCovered + ', \'' + change.Notes + '\')';
      }).join(', ')
  }};
  

• Screenshots:
  

  

  I am looking for advice or alternative methods to execute this bulk insert operation without encountering the string interpretation issue.
  Any insights or suggestions on how to handle JavaScript expressions in SQL mode effectively would be greatly appreciated.

  Thank you in advance for your assistance!

[ScottR]

Welcome to the forum!
Without seeing any screenshots I am going to assume that you are editing a table. If so, make sure you have a primary key set for the table component and then with a new query you should use GUI mode and select Bulk Update or Upsert using a primary key. Have you read through the documentation?

[Hayoung_Lee]

I solved the problem!
Not using SQL mode, Use GUI mode.

{{ 
    TableFTLMatch.changesetArray.map(item => {
    return {
      ...item,
      "InsPayId": TableSelectIc.selectedRow.InsPayID,
      "EobMethod" : InputReconcilingEobMethod.value,
      "EobDate" : InputReconcilingEobDate.formattedValue,
      "FtlDt" : now.value
    };
  })
}}

[jmann]

Linking another thread where we bulk inserted with SQL mode so that we can return the newly created records