I would like to use Ignition to move large sets of data from one database to another without iterating through each record. I would like to run a query on one database, and insert the result of this query in a table in another database. The SQL that I would use if the data were in the same database would be something like “INSERT into Table2 SELECT (column1, column2, column3) FROM Table1”. However the tables are in separate databases on separate machines; one is SQLServer and the other MySQL. What would be the most straightforward way to accomplish this?
Unfortunately, I think you are going to have to iterate through the records from the first database. You don’t have to create a separate INSERT command for each record going to the new database table. Your INSERT query can contain multiple records at once:INSERT INTO Table2 VALUES (1,2,'test'), (3,4,'test2"), .....If you don’t go down that path and you do create a separate INSERT query for each record, make sure you use transactions. You can find out more about transactions here:
If it is a one shot action and that you can use another program, we use to use Navicat for all those kind of things like transfert, data synchronisation etc across several db and os.
Just check Navicat Premium, this is the Ignition of dba…