# I need to SELECT data from MsSQL table and then insert to Mysql **URL:** \ **Category:** Ignition\ **Tags:** ignition79, ignition81\ **Created:** [October 12, 2023, 3:06pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397 "2023-10-12T15:06:54Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Vitaliy\_Galiy](https://avatars.discourse-cdn.com/v4/letter/v/3da27b/32.png) [@Vitaliy\_Galiy](https://forum.inductiveautomation.com/u/Vitaliy_Galiy)\ **Post date:** [October 12, 2023, 3:06pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/1 "2023-10-12T15:06:54Z")
Good day I have some problem when i try to work with tables, short: I have data that i take from Mssql and then i need to insert them to MySQL. If some data will change i need to hanlde the changes(Delete, Update, Add). I try to write some code in script but i have problem Here is some example of code: ``` def insert_update_tables(): query = "SELECT * FROM transfer_data" args = [] result = system.db.runPrepQuery(query, args, 'DB') query2 = "select cast(_IDRREF as bigint) as id, cast(_Fld11787RRef as bigint) as st_in, cast(_Fld11786RRef as bigint) as id_num, cast(_Fld13891RRef as bigint) as shift from _Document11782 where _Date_Time >=dateadd(day,-5,(DATEADD(year,-2000, SYSDATETIME()))) and _Marked =0 and _Fld11795=0 and _Posted =1" args1 = [] result2 = system.db.runPrepQuery(query2, args1, 'ALK') if result.getRowCount() > 0: pydata = system.dataset.toPyDataSet(result2) pydata1 = system.dataset.toPyDataSet(result) arr = [] editSet = set(tuple(row) for row in pydata) masterSet = set(tuple(row) for row in pydata1) deletions = [list(row) for row in (masterSet-editSet)] changes =[list(row) for row in (editSet-masterSet)] if result.getRowCount() == result2.getRowCount() and changes > 0: i = 0 for change_row in len(changes): change_row_insert = change_row[0 + i] query_take = "select cast(_IDRREF as bigint) as id, cast(_Fld11787RRef as bigint) as st_in, cast(_Fld11786RRef as bigint) as id_num, cast(_Fld13891RRef as bigint) as shift from _Document11782 WHERE id = {change_row_insert}" i = i + 1 dataset_to_update = system.db.runPrepAuery(query_take, [], 'ALK') if result.getRowCount() == result2.getRowCount() and changes == 0: pass if len(deletions) == 0: pass if result.getRowCount() == 0: for index in range(len(result2)): datacon = list(pdsData[index]) system.db.runPrepUpdate("INSERT INTO transfer_data(id, st_in, id_num, shift) VALUES(?, ?, ?, ?)", datacon, "DB") ``` Sorry for my english) Thank for help! ---
**Author:** ![Vitaliy\_Galiy](https://avatars.discourse-cdn.com/v4/letter/v/3da27b/32.png) [@Vitaliy\_Galiy](https://forum.inductiveautomation.com/u/Vitaliy_Galiy)\ **Post date:** [October 12, 2023, 3:08pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/2 "2023-10-12T15:08:34Z")
I try to TRUNCATE and them insert data but it take near 4 min Maybe u know some faster method? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [October 12, 2023, 3:33pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/3 "2023-10-12T15:33:26Z")
Which part takes four minutes; the dataset manipulation(s) in your script, or the actual SQL update(s) you're running? You're doing a _lot_ of looping over the input datasets, so if they're very large it's absolutely going to bog down. Where are you running this code from, also? Definitely don't run it in a client/designer scope, or there's extra network handshake and serialization latency to deal with. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [October 12, 2023, 4:06pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/4 "2023-10-12T16:06:00Z")
Is this a regular thing, or just a one time move to transition to another db ? ---
**Author:** ![Vitaliy\_Galiy](https://avatars.discourse-cdn.com/v4/letter/v/3da27b/32.png) [@Vitaliy\_Galiy](https://forum.inductiveautomation.com/u/Vitaliy_Galiy)\ **Post date:** [October 12, 2023, 4:13pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/5 "2023-10-12T16:13:31Z")
Dataset manipulation take 4 minutes (TRUNCATE table and insert data) I run into gateway script ---
**Author:** ![Vitaliy\_Galiy](https://avatars.discourse-cdn.com/v4/letter/v/3da27b/32.png) [@Vitaliy\_Galiy](https://forum.inductiveautomation.com/u/Vitaliy_Galiy)\ **Post date:** [October 12, 2023, 4:14pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/6 "2023-10-12T16:14:43Z")
Transition to another db it`s one time move, but check if they are similar and compare are regular ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [October 12, 2023, 4:39pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/7 "2023-10-12T16:39:36Z")
Might be simpler and faster to do outside of ignition. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [October 12, 2023, 4:57pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/8 "2023-10-12T16:57:21Z")
Section 2 on how to do it manually here (go from SQL Server to MySQL) - [Converting SQL Server to MySQL: 2 Easy Methods | Hevo](https://hevodata.com/learn/convert-sql-server-to-mysql/#t7) ---
**Author:** ![Vitaliy\_Galiy](https://avatars.discourse-cdn.com/v4/letter/v/3da27b/32.png) [@Vitaliy\_Galiy](https://forum.inductiveautomation.com/u/Vitaliy_Galiy)\ **Post date:** [October 12, 2023, 5:26pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/9 "2023-10-12T17:26:52Z")
I speak with my customer and he say "We need to do this in ignition designer" šŸ«¤ ---
**Author:** ![Vitaliy\_Galiy](https://avatars.discourse-cdn.com/v4/letter/v/3da27b/32.png) [@Vitaliy\_Galiy](https://forum.inductiveautomation.com/u/Vitaliy_Galiy)\ **Post date:** [October 12, 2023, 5:27pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/10 "2023-10-12T17:27:39Z")
I will cheсk this. TY ---
**Author:** ![DNice](https://avatars.discourse-cdn.com/v4/letter/d/858c86/32.png) [@DNice](https://forum.inductiveautomation.com/u/DNice)\ **Post date:** [October 12, 2023, 5:31pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/11 "2023-10-12T17:31:57Z")
You should be able to do this completely within MySQL with one or two statements. Likely you need to do some kind of UNION between the two tables and write any updated/missing records to the desired table. A number of examples available on Stack Overflow for what you are asking. ---
**Author:** ![Vitaliy\_Galiy](https://avatars.discourse-cdn.com/v4/letter/v/3da27b/32.png) [@Vitaliy\_Galiy](https://forum.inductiveautomation.com/u/Vitaliy_Galiy)\ **Post date:** [October 12, 2023, 5:53pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/12 "2023-10-12T17:53:19Z")
I can`t do this is mysql, main point i take data from MsSQL and insert them into Mysql. But when some data change in MsSQL i need change them in Mysql immediately ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [October 12, 2023, 6:05pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/13 "2023-10-12T18:05:32Z")
> [@Vitaliy\_Galiy](#): > > data change in MsSQL i need change them in Mysql immediately There is nothing in Ignition that will detect changes in MSSQL for you, no matter what you want to do with that knowledge. What is the point of this exercise? What is your customer trying to do? ---
**Author:** ![Vitaliy\_Galiy](https://avatars.discourse-cdn.com/v4/letter/v/3da27b/32.png) [@Vitaliy\_Galiy](https://forum.inductiveautomation.com/u/Vitaliy_Galiy)\ **Post date:** [October 12, 2023, 6:16pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/14 "2023-10-12T18:16:10Z")
My customer have some program "idk". This program send some data to MsSQL. My task: transver this data to Mysql. Every 2 minute check if data change or not. If change i need to Update my table in Mysql At the top i write some line of code, but idk how to solve this problem ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [October 12, 2023, 6:17pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/15 "2023-10-12T18:17:21Z")
This program that is sending it to MsSQL - is it Ignition or some other program? Any reason why it can't send data directly to your MySQL? ---
**Author:** ![Vitaliy\_Galiy](https://avatars.discourse-cdn.com/v4/letter/v/3da27b/32.png) [@Vitaliy\_Galiy](https://forum.inductiveautomation.com/u/Vitaliy_Galiy)\ **Post date:** [October 12, 2023, 6:19pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/16 "2023-10-12T18:19:29Z")
It some Ukraine program, she can`t send data to MySQL ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [October 12, 2023, 6:20pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/17 "2023-10-12T18:20:34Z")
Ignition is connected to both databases, right? If the data is maintained "live" in MS SQL, why do you need it in MySQL? Ignition can query either database. Please explain _ **why your customer needs this data in MySQL** _, instead of just using the data in MS SQL. ---
**Author:** ![Vitaliy\_Galiy](https://avatars.discourse-cdn.com/v4/letter/v/3da27b/32.png) [@Vitaliy\_Galiy](https://forum.inductiveautomation.com/u/Vitaliy_Galiy)\ **Post date:** [October 12, 2023, 6:23pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/18 "2023-10-12T18:23:43Z")
Yeah, connected to both, To be honest i dont know why they need in MySQl (maybe in future it will be some logick, idk) I just have a task to do this, That's all Thank for your help! ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [October 12, 2023, 6:27pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/19 "2023-10-12T18:27:10Z")
If they're trying to do a one time migration, then just do that - follow the article I posted above, migrate all the data over, and then exclusively use MySQL afterward. Or even in your designer if the script is slow - who cares because you're only running it once. If they want replication, then use another MsSQL server and you have built-in options of setting that up outside of Ignition. You're basically being asked to code replication of MsSQL into a MySQL database and 1) I don't see the point in that, there's probably bound to be some issue at some point tbh and 2) it's going to be a lot of work for something you can get built-in if you just use another MsSQL server instead of MySQL ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [October 12, 2023, 6:32pm UTC](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397/20 "2023-10-12T18:32:54Z")
So, you will need a timer script in your gateway that will perform the follow steps: - Query the current data from MS SQL, - Query the current data from MySQL, - Compare the two results row by row and column by column with the rows, - If any item is different or rows are missing or extra, replace the MySQL data. That last item would be done something like this: - Start a transaction with [system.db.beginTransaction()](https://docs.inductiveautomation.com/display/DOC81/system.db.beginTransaction) for the following items: - Delete all of the current rows from MySQL, - Run multi-value inserts from the MS SQL query results to insert the new data into MySQL, - Commit the transaction. MySQL can only do transactions when the table type is InnoDB, not classic. IIRC. Be sure MySQL's table is set up correctly. [Next page](https://forum.inductiveautomation.com/t/i-need-to-select-data-from-mssql-table-and-then-insert-to-mysql/80397.md?page=2)