# How to insert lots of data with a single message in sqlite
**URL:** https://discourse.nodered.org/t/how-to-insert-lots-of-data-with-a-single-message-in-sqlite/4089
**Category:** General
**Created:** [18 October 2018 13:15 UTC](https://discourse.nodered.org/t/how-to-insert-lots-of-data-with-a-single-message-in-sqlite/4089 "2018-10-18T13:15:33Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Andres](https://discourse.nodered.org/u/Andres)
#### Post date: [18 October 2018 13:15 UTC](https://discourse.nodered.org/t/how-to-insert-lots-of-data-with-a-single-message-in-sqlite/4089/1 "2018-10-18T13:15:33Z")
Hi everyone! I have a sqlite DB and i need to insert around 60000 new entrities, if i insert entry by entry, this takes lots of time (last time i do that, it took over 3 hours, don't know why) and freezes NR, how can i do this with a single message?
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [18 October 2018 13:39 UTC](https://discourse.nodered.org/t/how-to-insert-lots-of-data-with-a-single-message-in-sqlite/4089/2 "2018-10-18T13:39:20Z")
So you are saying you need to insert 60,000 new rows...right?
how many fields in each row?
How big are each of the fields?
How many rows are in the DB already?
How much activity will there be against the data per day?
I'm trying to get a feel for the volume of data you are dealing with.
---
### Author:  [@Andres](https://discourse.nodered.org/u/Andres)
#### Post date: [18 October 2018 14:09 UTC](https://discourse.nodered.org/t/how-to-insert-lots-of-data-with-a-single-message-in-sqlite/4089/3 "2018-10-18T14:09:18Z")
> [@zenofmud](#):
>
> So you are saying you need to insert 60,000 new rows...right?
exactly
> [@zenofmud](#):
>
> how many fields in each row?
the data comes from an excel file wich has 75 columns and between 2000 and 30000 rows, in one of the column there is a description of wich table has to go the row, but that row can go to 2 or more tables, so some times i have 60k new rows.
> [@zenofmud](#):
>
> How big are each of the fields?
depend on the field, the largest is 100 chars, the lowest is 1 char
> [@zenofmud](#):
>
> How many rows are in the DB already?
the DB is recently new, and some tables grow faster than others, the largest has 47000, but is also the most recent one.
> [@zenofmud](#):
>
> How much activity will there be against the data per day?
around 10000 rows are updated every day, but it will reach 30k updates per day
every two or three days i upload an excel file to the system
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [18 October 2018 14:58 UTC](https://discourse.nodered.org/t/how-to-insert-lots-of-data-with-a-single-message-in-sqlite/4089/4 "2018-10-18T14:58:24Z")
Well, this isn't really a Node-RED question - but still.
You will need to understand prepared statements and batch updates.
In addition, with such a large batch of updates, you might consider dropping then rebuilding your indexes as this may be quicker.
However, I'd say that this is really beyond reasonable for SQLite. You are likely to be better off with a managed database like Postgres. Alternatively, if the data is time-based, I'd go with something like InfluxDB which is specifically tuned to timeseries data.
Oh, and if your Excel file is in the right format, you may find a native data upload tool that is more efficient at loading the whole file.
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [18 October 2018 15:02 UTC](https://discourse.nodered.org/t/how-to-insert-lots-of-data-with-a-single-message-in-sqlite/4089/5 "2018-10-18T15:02:00Z")
Is all the data going to be new or are you going to be updating some of it? i.e. on day 1 you might insert 60,000 rows, but on day 20 might some of the rows be replacing rows that already exist?
What platform are you running NR on?
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [18 October 2018 18:33 UTC](https://discourse.nodered.org/t/how-to-insert-lots-of-data-with-a-single-message-in-sqlite/4089/6 "2018-10-18T18:33:43Z")
any rate the formate is this:
```auto
INSERT INTO table1 (
column1,
column2 ,..)
VALUES
(
value1,
value2 ,...),
(
value1,
value2 ,...),
...
(
value1,
value2 ,...);
```
although, you are still going to have to build the strings of data to insert. Have you thought about using something other than NR to load the data? try googling 'insert cvs into sqlite' and 'insert excel into sqlite'
---
### Author:  [@Andres](https://discourse.nodered.org/u/Andres)
#### Post date: [22 October 2018 13:36 UTC](https://discourse.nodered.org/t/how-to-insert-lots-of-data-with-a-single-message-in-sqlite/4089/7 "2018-10-22T13:36:51Z")
sorry for the delay on the answer. Your solution was what i was looking for, Thank you!