# Select Data from SQLite and insert into Azure SQL Server

**URL:** <https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127>\
**Category:** General\
**Created:** [14 May 2019 16:24 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127 "2019-05-14T16:24:10Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![thagan](https://avatars.discourse-cdn.com/v4/letter/t/3d9bf3/32.png) [@thagan](https://discourse.nodered.org/u/thagan)\
**Post date:** [14 May 2019 16:24 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/1 "2019-05-14T16:24:10Z")

</div>

Hello,

I have a Raspberry Pi that is pulling data from a SMA Solar inverter and storing it in a SQLite database every (1) minute. I want to use Node-Red to copy/sync that data up to a SQL Server I have in Azure.

I have my flow configured and can read the data from SQLite and I also have my connection to Azure SQL working. My issue or lack of knowledge is how do I take that data and insert it into SQL?

If its a Function what do I need in the function? Do I write the insert query in the SQL\_Azure node? I am playing around with everything for the past few days and just can't get it. Any help would be so grateful.

Node-Red Version: v0.20.5  
SQLite Version: 3.8.7.1  
SQL Server Version: 2016

Regards

Tim

 ![Flow](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/8/8c466fc27357b0f27d1279b9098ed8156d7fda28.jpeg)

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [15 May 2019 08:27 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/2 "2019-05-15T08:27:19Z")

</div>

Do you know the Azure SQL command that you would use to insert the data manually? I'd start there. I'm afraid I don't have more specific help as I don't use Azure SQL myself.

---

<div class="post-metadata">

**Author:** ![thagan](https://avatars.discourse-cdn.com/v4/letter/t/3d9bf3/32.png) [@thagan](https://discourse.nodered.org/u/thagan)\
**Post date:** [15 May 2019 11:33 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/3 "2019-05-15T11:33:54Z")

</div>

Julian,

Thank you....It’s just a VM of SQL in a Azure domain. So it’s your normal SQL statements you use.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [15 May 2019 12:15 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/4 "2019-05-15T12:15:18Z")

</div>

> [@thagan](#):
>
> it’s your normal SQL statements you use.

Well then haven't you answered your own question? create an sql statement to do the inserts. As I don't have access t Azure, I don't know what options it has. If it follows the lead of the mysql node, you can put the sql in msg.topic. If you want to nsert multiple rows at a time, do a search of the forum, this has come up a couple of times recently.

---

<div class="post-metadata">

**Author:** ![thagan](https://avatars.discourse-cdn.com/v4/letter/t/3d9bf3/32.png) [@thagan](https://discourse.nodered.org/u/thagan)\
**Post date:** [15 May 2019 13:57 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/5 "2019-05-15T13:57:12Z")

</div>

\*\* Yes the SQL insert side I have down. There is nothing special about this Azure, its your normal SQL server database. My issue I am having is writing the SQL  
script in the function NODE to grab the SQLite payload msg. For example.\*\*

\*\* This is my SQLite result. I want to take the Pdc1: value and insert it into my SQL table column (Topic). The insert query is easy. But the query to GRAB the  
payload msg I get from the SQLite table I can’t get down.\*\*

 ![](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/a/a573ffb5da251d8c22c517b7794251762442b06e.jpeg)

 ![](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/4/4abaf1089ca6547be47d5040b361c98c84ca41d9.jpeg)

 ![](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/5/5b99144ce8ff17e0628a90ffc6d32d0b6270eab6.jpeg)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [15 May 2019 14:38 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/6 "2019-05-15T14:38:32Z")

</div>

See the red X's in the function node - those are errors. You can't split assignment statements across lines. Put the msg.topic = ..... all on one line.

---

<div class="post-metadata">

**Author:** ![thagan](https://avatars.discourse-cdn.com/v4/letter/t/3d9bf3/32.png) [@thagan](https://discourse.nodered.org/u/thagan)\
**Post date:** [15 May 2019 20:18 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/7 "2019-05-15T20:18:26Z")

</div>

Thank you Paul. That I did not know. That fixed the error on the function. If I run this in SQL it works but trying to adapt it on Node Red gives me the below error. I am just  
missing something still.

 ![](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/a/a1b22677472c2871d0f5fe5c6044a04f02a8fcea.jpeg)

![](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/e6bcf8718e0795519756fe6bf61cfc759ff5259b.png)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [15 May 2019 20:21 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/8 "2019-05-15T20:21:21Z")

</div>

Paste the function node code in a reply, there is probable an error in it.  
You could also attach a debug node (display complete msg object) to the output of th efunction node to see what you are creating.

---

<div class="post-metadata">

**Author:** ![thagan](https://avatars.discourse-cdn.com/v4/letter/t/3d9bf3/32.png) [@thagan](https://discourse.nodered.org/u/thagan)\
**Post date:** [17 May 2019 13:42 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/9 "2019-05-17T13:42:15Z")

</div>

Here is the function node. It reaches my SQL but it insets and undefined record.

pld = "INSERT INTO [SMA].[dbo].[test] "  
pld = pld + "(test) "  
pld = pld + "VALUES ('" + msg.payload.Pdc1 + "')";  
msg.topic = ''  
msg.payload = pld  
return msg;

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/b/b3ea16809fc06feb8f2bb0df01ec03d52fa1bb6f.png)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/e/e83502e498bafee4e7bcbb59811417bb3b277bba.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 May 2019 13:57 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/11 "2019-05-17T13:57:00Z")

</div>

The query has to be in the topic, as you originally showed. Then follow @zenofmud's advice and put a debug node showing complete message on the function node output to check that it looks ok.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [17 May 2019 20:20 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/12 "2019-05-17T20:20:04Z")

</div>

> [@thagan](#):
>
> pld = pld + "VALUES ('" + msg.payload.Pdc1 + "')";

Looks like that payload is an array so `msg.payload.Pdc1` would be `msg.payload[0].Pdc1` where 0 is the number of the array index you want.

---

<div class="post-metadata">

**Author:** ![poonamyr](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/poonamyr/32/39801_2.png) [@poonamyr](https://discourse.nodered.org/u/poonamyr)\
**Post date:** [5 April 2021 12:24 UTC](https://discourse.nodered.org/t/select-data-from-sqlite-and-insert-into-azure-sql-server/11127/13 "2021-04-05T12:24:16Z")

</div>

Hello Tim,  
Were you able to get through the replication of data from SQLite to Azure SQL Server ?

I am looking for the same data integration and was wondering Node Red did the trick. I am totally new to Node Red, we were thinking of using ADF.