How to do dynamic inserts in sql

App Building
1

When using SQL or GUI how can i make bulk inserts in sql with dynamiz data, for example:

const newContactId = localStorage.values.newContactId;
const selectedItems = addUserWorkspaces.selectedItems;
const values = selectedItems.map(item => `('${newContactId}', '${item.id}')`).join(", ");

const sqlQuery = `
    INSERT INTO "UsersToCustomers" ("userId", "customerId")
    VALUES ${values};
`;

// Now you can execute the sqlQuery
2

When using the GUI query editor, you can select the "Bulk insert records" action and reference your data directly in the editor. Additionally, you can change or filter your data in the editor before inserting it into the database.

E.g.
image

If your mapping is particularly complex, I recommend moving it into a separate transformer. This way, you only need to reference the transformer in your insert query, improving readability and maintainability.

3 Likes
3

I agree with Tobias.

I think (don't quote me) there is no dynamic SQL query function eg sqlExecute(sqlQuery) because of issues like prepared statements.

The idiomatic way to do it is with a resource query as per Tobias or you can always use .trigger() with AdditionalScope for data injection.

It's not an exact science and there are a few ways to do it but Retool forces you to avoid dynamic SQL in javascript to the best of my knowledge.

1 Like
4

Some documentation on prepared statements and how to turn them off here:

1 Like