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

Toad Data Point & Toad Intelligence Central
1

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

2

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.

image
image1415×761 105 KB