Bulk Insert Data in a Database

[Teodor]

Intro

Bulk insert actions involve inserting a large amount of data into a database table in a single operation, which is more efficient than individual row inserts. This method improves performance by reducing overhead, minimizing network traffic, and optimizing logging and indexing. Bulk insert actions are particularly useful for CSV imports.

NOTE: Transactions and Bulk Insert are only supported in PHP and NodeJS. They are not available for ASP/ASP.NET

In this tutorial we will show you how to bulk insert data from a CSV file.

Bulk Insert CSV data

We start by creating a server action in the Server Connect panel:

We add a name for it:

In this example we will show you how to import data from a CSV file, located on our server. Of course you can implement the same functionality for cases where you need CSV upload and insert, or CSV export from one table and then insert in another.

We add a Import CSV File step in the Server Action steps:

Then we select the CSV path. As we mentioned already - this can be a file on the server or a dynamic path returned from an upload step:

We select a file on our server:

We setup the Import Fields and Header options, so that they match the CSV structure:

The next step is to add the Bulk Insert action. Open Database Actions and add the Database Bulk Insert:

Select the Bulk Insert Source:

In our case this is the CSV Import step:

Set the batch size. The batch size refers to the number of records that are inserted into the database in each iteration of the bulk insert operation:

And then set the Bulk Insert Options:

Select the table to insert data into:

Then select the values for your database table fields:

You can choose the values from the dropdown:

Click OK when you are done:

And we are ready. In this case we can directly run the Server Action in the browser, in order to execute the bulk insert:

And you can see the data has been imported in the database table:

On Error

In the event that the Bulk Insert action encounters an issue and fails before completion, any changes made by the operation within the database will be automatically reverted.

Benchmark

We compared the different data insert methods - Repeat with Insert and Bulk Insert.
Results:

Inserting 1.000 Records in a database
Repeat with Insert: 8612ms
Bulk Insert: 15ms

Inserting 10.000 Records in a database
Repeat with Insert: 120000ms (2 minutes)
Bulk Insert: 100ms

[Hyperbytes]

@Teodor

The batch size refers to the number of records that are inserted into the database in each iteration of the bulk insert operation:

Any indication what size would be optimal?
Small batches or 1000s?

[patrick]

It is difficult to say which batchSize is the most optimal as it may differ per database and also depends on the data (number of columns) being inserted per record.

In PHP the batchSize is being ignored and it uses prepared statements for all inserts, so the sql statement only has to compile once and then being executed with the parameters.

In NodeJS the batchSize is used to generate insert statements that inserts the number of rows given in the batchSize.

OfCourse all of this is done in a single transaction and it will rollback the transaction when there was an error, so no half imported data.

[Hyperbytes]

Thanks, want sure how it was implemented so was unsure.

I will assume as big as memory reasonably allows.

Will do some tests when back home, interested in how this could perform as a scheduled data backup option to another server.

[Hyperbytes]

Final question @patrick if I may.
Think I know the answer but want to be sure. if I was to do:

Query all from database table 1
Transaction
          Delete all from database table 2
         Bulk  Insert into table 2 from query

Failure in the bulk insert would also rollback the delete as it in within the transaction?

[psweb]

Ok, well this was just about the most impressive thing i have seen in a while.

I have a 2.14 GB file which is compressed as a .zst file
The file decompresses to 28.27 GB as a JSONL file.

Using bulk insert I was able to insert 2.5 million rows as a single column insert into a database table, and it only took 34 seconds. The exact number of records was 2 585 753.

Thank you, i tried a few other methods first, which were taking about 6 hours and timing out after about 900k records.

[Hyperbytes]

So you were able to do it directly from the jsonl file without first converting to true json?

Can you share how you defined the jsonl file to be the data source?

[psweb]

Sure, still getting it perfect, but i will share the final modules with you afterwards for sure.