Toad World® Forums

Insert/update/delete data into one database type from another

I am brand new to Data Point, so this might be a simple question but I'm struggling to figure it out.

I want to read data via a query from mysql and insert data into SQL Server.

For example

Insert into SQLServerDB.Table (Field1,field2)
select field1,field2
from MySQLdb.table;

Update SQLServerDB.table
set field1 = mysql.field1,
field2 = mysql.field2
where id = mysql.id;

delete from SQLServerdb.table
where id in (select id from mysqldb.table);

I think that shows what I'm wanting to do.

Can someone point me in the right direction?

Thanks
Alan

Welcome to the Toad Data Point forum!

Neither Toad Data Point's Visual Query Builder nor the Editor were designed to support heterogeneous DML statements (e.g. Inserts/Updates/Deletes)
although they do support heterogeneous SELECTs.

However, you might be pleased to know that you can get pretty close to your requirement--at least for INSERTS--by using the Import-Export Data Wizard, per snap below. Not all datatypes across data different source platforms are compatible or mappable, so keep that in mind to make sure the cross-platform migration is do-able.

Otherwise, next best thing might be to use TDP's Automation Engine to create a workflow that accomplishes what you're trying to do. That is, you can create an visual automation script that will:

  • Loop through each record of your MySQL table (the driving table)
  • Assign variable values for each desired column of the current MySQL row
  • Execute your desired DML statement/s that include variable references to the MySQL column values to alter your SQL Server target table

Hope this gives you some ideas.