# MySQL data transfer to MSSQL
**URL:** https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736
**Category:** General
**Tags:** database
**Created:** [26 October 2021 05:51 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736 "2021-10-26T05:51:56Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@idlvpfk](https://discourse.nodered.org/u/idlvpfk)
#### Post date: [26 October 2021 05:51 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/1 "2021-10-26T05:51:56Z")
Good morning,
Again im here for help!
What Im trying to achieve:
_Task is to import all MySQL entries into MSSQL server._
_Whenever user fills php form these data must be sent to MySQL and MSSQL._
Where Im stucked now:
_I dont know how to push these values (for example payload[0].id ) into MSSQL, and what should be written into function node.
Injection node msg.topic: SELECT \* FROM input\_data ORDER BY id DESC LIMIT 1;_
Question:
_Is it even possible to make this thing automatic? Something like watchdog for MySQL DB?_
Every help will be appreciated! Thanks!
---
### Author:  [@idlvpfk](https://discourse.nodered.org/u/idlvpfk)
#### Post date: [26 October 2021 08:23 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/2 "2021-10-26T08:23:00Z")
Now when i select INJECT, it sends data successfully, but i need to find solution for scenario: if only new ID is created, only then push data into MSSQL. Hope for some ideas! Thanks!
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [26 October 2021 09:48 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/3 "2021-10-26T09:48:26Z")
If you have an Identity column (hopefully you do 😉 )
You can use `IF NOT EXISTS`
`IF NOT EXISTS (SELECT ID FROM input_data WHERE ID = payload[0].id) INSERT INTO ...`
maybe you can use that approach
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [26 October 2021 09:55 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/4 "2021-10-26T09:55:22Z")
I would also encourage the use of template here - makes the SQL a lot easier to deal with.
using brackets to fill in the place holders `{{some.object.path}}`
---
### Author:  [@idlvpfk](https://discourse.nodered.org/u/idlvpfk)
#### Post date: [26 October 2021 10:00 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/5 "2021-10-26T10:00:27Z")
Marcus, thanks mate!! Ill
I can see, that youre good at this. May i ask another question?
How can i import all entries which are located in mysql till today? when i try to inject `SELECT * FROM input_data ORDER BY id DESC LIMIT 1000 ;` it only returns 1 entry instead of 1000.
I need to transfer existing data to MSSQL at first, and then i should implement IF `NOT EXISTS` .
Thanks mate for reaching your hand in this issue, as im very green in node-red exploration.
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [26 October 2021 10:11 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/6 "2021-10-26T10:11:07Z")
> I can see, that you're good at this
Lets not jump ahead of our self 😉
I don't do MySQL and I am unfamiliar with LIMIT.
in MsSQL the equivalent is:
`SELECT TOP 1000 * FROM input_data order by id DESC`
I am not sure of the output from the MySQL node - so a little blind here,
but can you instead employ a where clause `WHERE date < today`?
---
### Author:  [@idlvpfk](https://discourse.nodered.org/u/idlvpfk)
#### Post date: [26 October 2021 10:41 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/7 "2021-10-26T10:41:45Z")
When I insert `WHERE date < today` in MySql injector, syntax is incorrect.
` SELECT * FROM input_data WHERE date < today`
Maybe only soltuion is to reverse asking in this case, when MSSQL should ask MySQL for all entries?
Or inject node should contain i++ variable?
Grrrrr,
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [26 October 2021 10:42 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/8 "2021-10-26T10:42:01Z")
> [@idlvpfk](#):
>
> SELECT \* FROM input\_data ORDER BY id DESC LIMIT 1000
That should work. Are you sure it only returns one record? Have you got a debug node directly on the output of the Mysql node?
What happens if you take off the limit and the order by?
---
### Author:  [@idlvpfk](https://discourse.nodered.org/u/idlvpfk)
#### Post date: [26 October 2021 10:48 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/9 "2021-10-26T10:48:22Z")
> [@Colin](#):
>
> > [@idlvpfk](#):
> >
> > SELECT \* FROM input\_data ORDER BY id DESC LIMIT 1000
Thanks for idea, but yes, one inject returs only one msg.payload , which contains one Mysql entry
Maybe my node config is really messed up
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [26 October 2021 10:49 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/10 "2021-10-26T10:49:27Z")
As @Colin highlights - it really should work.
but on this
`WHERE date < today`
`today` should be todays date (or the MySQL date function) MsSQL is `getDate()`
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [26 October 2021 10:53 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/11 "2021-10-26T10:53:41Z")
Whats this doing?
Is it filtering to the last row 😆 - hence you wont get an array?
---
### Author:  [@idlvpfk](https://discourse.nodered.org/u/idlvpfk)
#### Post date: [26 October 2021 10:57 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/12 "2021-10-26T10:57:23Z")
Flow which Im @ right now
```auto
[{"id":"ba997b9ccfe7069d","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"5a7faf4857ebe3de","type":"debug","z":"ba997b9ccfe7069d","name":"DEBUG SHOW ALL","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1220,"y":360,"wires":[]},{"id":"765669b67b92638b","type":"mysql","z":"ba997b9ccfe7069d","mydb":"9c3eddb86f5612e1","name":"","x":570,"y":360,"wires":[["ab9d55f5689a1ece"]]},{"id":"2e1d403b394b363a","type":"inject","z":"ba997b9ccfe7069d","name":"SELECT 1 LIMIT 1;","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":" SELECT * FROM input_data ORDER BY id DESC LIMIT 100","payloadType":"str","x":350,"y":360,"wires":[["765669b67b92638b"]]},{"id":"9095a0d4a61617f7","type":"MSSQL","z":"ba997b9ccfe7069d","mssqlCN":"0e11ab78f066762c","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"\r\
```
---
### Author:  [@idlvpfk](https://discourse.nodered.org/u/idlvpfk)
#### Post date: [26 October 2021 10:58 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/13 "2021-10-26T10:58:30Z")
just checking if message is reaching debugger...
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [26 October 2021 10:59 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/14 "2021-10-26T10:59:51Z")
I (think) I see the problem,
you are only passing row 0
```auto
msg.payload = {
id: msg.payload[0].id,
kritusie_putni: msg.payload[0].kritusie_putni,
izbr_putni: msg.payload[0].izbr_putni,
bar_pat:msg.payload[0].bar_pat,
gaz_pat:msg.payload[0].gaz_pat,
gaz_pat_day:msg.payload[0].gaz_pat_day,
gaz_pat_night:msg.payload[0].gaz_pat_night,
ele_pat:msg.payload[0].ele_pat,
ude_pat:msg.payload[0].ude_pat,
location:msg.payload[0].location,
fordate:msg.payload[0].fordate,
date:msg.payload[0].date,
username:msg.payload[0].username
}
return msg;
```
the result is an array (not a message per row)
here you are only using row 0 from that array
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [26 October 2021 11:04 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/15 "2021-10-26T11:04:01Z")
Put a split node after the MySQL.
then for each msg the split churns out
```auto
msg.payload = {
id: msg.payload.id,
kritusie_putni: msg.payload.kritusie_putni,
....
}
return msg;
```
---
### Author:  [@idlvpfk](https://discourse.nodered.org/u/idlvpfk)
#### Post date: [26 October 2021 11:17 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/16 "2021-10-26T11:17:47Z")
MARCUS!! YOU DID IT! You found glitch in my nodes!
Now MySQL limit counter is being pushed in MSSQL! If limit is 5 , 5 new entries are pushed into MSSQL!
Ok, now I have to figure it out, how to push **only** unique ID(latest). And after that i can use timer, for example 1x5min to check MySQL if there is new ID.
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [26 October 2021 11:28 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/17 "2021-10-26T11:28:59Z")
Put a template node directly after the split.
Then your MsSQL after the template.
Use this as your template.
```auto
IF NOT EXISTS (SELECT id FROM input_data WHERE id = '{{payload.id}}')
INSERT INTO input_data (id, other_col, other_col)
VALUES ('{{payload.id}}','{{payload.other_col}}','{{payload.other_col}}')
```
Disclaimer: there may be other ways, and this may have errors - but this gives you some control
---
### Author:  [@idlvpfk](https://discourse.nodered.org/u/idlvpfk)
#### Post date: [26 October 2021 11:46 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/18 "2021-10-26T11:46:10Z")
Now im getting all fields as NULL
---
### Author:  [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)
#### Post date: [26 October 2021 11:48 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/19 "2021-10-26T11:48:44Z")
capture one of the commands in the debug - and run it within SMS as a new query.
---
### Author:  [@idlvpfk](https://discourse.nodered.org/u/idlvpfk)
#### Post date: [26 October 2021 11:59 UTC](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736/20 "2021-10-26T11:59:59Z")
Im sorry Marcus, but i dont understand what you mean by SMS.
Here is debug node in each step.
Sorry for my incompetence.
[Next page](https://discourse.nodered.org/t/mysql-data-transfer-to-mssql/52736.md?page=2)