Bulk insert - troubleshooting

[rileydog]

Hello,

I have single row inserting working great - but I am now wanting to do a bulk insert because often I am trying to insert hundreds of rows at a time and performing this insert for each row takes a LONG time...

I have three columns in this destination table - client_UUID, contract_UUID and URL_path

the URL_path is contained a Retool UI table called "FilesTable", column name is "URL_path" - whereas client_UUID and contract_UUID are columns in a selected row column on another table.

So this is the "Array to records to insert" code snippet:

{{ [{client_UUID: ClientTable.selectedRow.UUID}, {contract_UUID: ContractTable.selectedRow.UUID}, {URL_path: FileStaging.data}] }}

Of course it fails miserably - I am thinking I may need to do a transform? I have never used that before so any help would be appreciated!

Basically, in pseudo code I need the VALUES to be

{{client_UUID: ClientTable.selectedRow.UUID, contract_UUID: ContractTable.selectedRow.UUID, URL_path: <<This is an array of column "URL_Path" in FileStaging>>

Thanks again!

[rileydog]

I think I am very close but still can use help.

I have a SQL insert that works perfectly as follows:

INSERT INTO `hybrid-task-358121.config_panel.files_to_ingest` 
(client_UUID, contract_UUID, URL_path)
VALUES
('b047e850-a3a2-4943-8a9a-25125e6f7443', '24c9e6d2-039c-482e-b08c-879db4dafd81', 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H008_ffs_in-network.json.gz'), ('b047e850-a3a2-4943-8a9a-25125e6f7443', '24c9e6d2-039c-482e-b08c-879db4dafd81', 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H027_ffs_in-network.json.gz'), ('b047e850-a3a2-4943-8a9a-25125e6f7443', '24c9e6d2-039c-482e-b08c-879db4dafd81', 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H017_ffs_in-network.json.gz'), ('b047e850-a3a2-4943-8a9a-25125e6f7443', '24c9e6d2-039c-482e-b08c-879db4dafd81', 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H009_ffs_in-network.json.gz'), ('b047e850-a3a2-4943-8a9a-25125e6f7443', '24c9e6d2-039c-482e-b08c-879db4dafd81', 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H023_ffs_in-network.json.gz');

But when I convert this query (called FilesQuery2 for now) to try to do additional scope:

INSERT INTO `hybrid-task-358121.config_panel.files_to_ingest` 
(client_UUID, contract_UUID, URL_path)
VALUES
{{ sql_code }};

And trigger this using:

  FilesQuery2.trigger({
    additionalScope: { sql_code: sql }, 
    onSuccess: '',
    onFailure: '',
  });

Where I can confirm the sql variable (a string) does contain the following:

"('b047e850-a3a2-4943-8a9a-25125e6f7443', '24c9e6d2-039c-482e-b08c-879db4dafd81', 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H008_ffs_in-network.json.gz'), ('b047e850-a3a2-4943-8a9a-25125e6f7443', '24c9e6d2-039c-482e-b08c-879db4dafd81', 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H027_ffs_in-network.json.gz'), ('b047e850-a3a2-4943-8a9a-25125e6f7443', '24c9e6d2-039c-482e-b08c-879db4dafd81', 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H017_ffs_in-network.json.gz'), ('b047e850-a3a2-4943-8a9a-25125e6f7443', '24c9e6d2-039c-482e-b08c-879db4dafd81', 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H009_ffs_in-network.json.gz'), ('b047e850-a3a2-4943-8a9a-25125e6f7443', '24c9e6d2-039c-482e-b08c-879db4dafd81', 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H023_ffs_in-network.json.gz')"

And the query fails - I get Syntax error: Unexpected INSERT target name at [3:1]

I paste that sql string value manually into that insert and it works fine.

Any ideas? Thanks,

[jocen]

Welcome to the community @rileydog
Have you tried GUI mode for this one? Retool recommends using GUI mode for insert/write actions.

[rileydog]

Hello - thanks for the reply - yes, tried both ways. used additional scope and got the same error too.

[rileydog]

Could it be my string contains the ' character? The string I am trying to "insert" is:

[{ client_UUID: 'b047e850-a3a2-4943-8a9a-25125e6f7443', contract_UUID: '24c9e6d2-039c-482e-b08c-879db4dafd81', URL_path: 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H008_ffs_in-network.json.gz'}, { client_UUID: 'b047e850-a3a2-4943-8a9a-25125e6f7443', contract_UUID: '24c9e6d2-039c-482e-b08c-879db4dafd81', URL_path: 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H027_ffs_in-network.json.gz'}, { client_UUID: 'b047e850-a3a2-4943-8a9a-25125e6f7443', contract_UUID: '24c9e6d2-039c-482e-b08c-879db4dafd81', URL_path: 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H017_ffs_in-network.json.gz'}, { client_UUID: 'b047e850-a3a2-4943-8a9a-25125e6f7443', contract_UUID: '24c9e6d2-039c-482e-b08c-879db4dafd81', URL_path: 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H009_ffs_in-network.json.gz'}, { client_UUID: 'b047e850-a3a2-4943-8a9a-25125e6f7443', contract_UUID: '24c9e6d2-039c-482e-b08c-879db4dafd81', URL_path: 'https://bcbsla.sapphiremrfhub.com/mrfs/202306/2023-04-27_bcbsla_H023_ffs_in-network.json.gz'}]

So that string contain apostrophe characters...

[rileydog]

Maybe I am going about this the wrong way.

What I am trying to do is bulk insert - the issue I have is the single row insert (which works perfectly fine) is very slow when I have 20+ rows to add to the table. I was hoping to do a single insert command for all those rows to add...

My source table contains one column (in this case URL_path), and that is the correct destination column name too. But to insert into the destination table requires two other columns which are constants (not arrays) - client_UUID and contract_UUID.

Would a transform work for this use case? What is the best approach to accomplish this?

[joeBumbaca]

Hey @rileydog! Yeah, you are close!! Looks like each value in the bulk insert has a couple of static values and one that changes for that set of inserts. The best way to do this is probably with a JS query, to construct the array. You can either use that return value of the JS query in the bulk insert, or trigger it and pass it through additionalScope. In this example, I've used additionalScope.

Use the additionalScope variable in the bulkInsert query

Let me know if you have any follow up questions!

[rileydog]

YES!!! A virtual beer (or your favorite beverage) to you sir! Works perfect. All the best...