Migrate table from SQL server to MySQL

Question

Migrate table from SQL server to MySQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

-1

I have 2 systems (One in asp and one in PHP ) The one in asp is connected to SQL server and PHP to MySQL.

In SQL Server I have a table called "Clients". This table is updated every 2 or 3 days.

In MySQL , I have replicated that same client table that feeds the system in PHP .

At the moment they are working "OFFLINE", that is to say, the "Clients" table of MySQL is never updated, since it is a process that is done manually every 2 weeks.

My question is if there is the possibility of making a script or something that allows the update automatically every day, or manually, but that is simple, such as pressing a button to run the script and update.

The way I'm doing now is to export from the SQL Server table "clients", and then make an import in MySQL.

But this is a manual task that I can not always do, so I need an alternative so that someone with little knowledge can do it without any problem.

I detail a little more how I perform the process that takes me a long time and I run the risk of making mistakes along the way. (There are times when someone has to do "NO TECHNICAL")

Currently what I do is export a csv, load it into an auxiliary table ( MySQL ) and then perform the query to insert from the auxiliary table ( MySQL ) to the main table ( MySQL )

Is there any other way to do this?

I tried several things but I did not succeed until now:

From access with php to SQL Server until you link and configure the servers.

None of that worked for me.

Is there the possibility of generating something similar to automate the process I described at the beginning?

mysql php asp sql-server

asked by Pepemujica 31.10.2016 в 13:59

source

2 answers

Problems with SQLite [closed] dynamically change the connection of a dbcontext

score 0 · Answer 1

Initially, if the SQL Server table is updated every 2 or 3 days, you could update the process that updates that table so that it also updates the MySQL table. There would be no problem in using ODBC to connect to MySQL from the same application that connects to SQL Server.

I am limited to answer you for your comment about "few knowledge", maybe you can expand on your possibilities.

The task you do manually (export SQL Server -> import into MySQL) could automate it with some script, be it bash (Linux) or vbs (Windows) and then use cronjob (Linux) or Scheduled Tasks (Windows) to run once per day, per hour, per week or as you want to program it.

score 0 · Answer 2

In your particular case you need the updated information in both SMBD , I recommend you investigate about linked servers ( linked servers ), in order to insert directly from SQL Server to the table in MySQL , either through a stored procedure or a trigger directly in your table clients.

Once you have your linked server configured, you can insert MySQL as follows

INSERT INTO OPENQUERY (MYSQL, 'SELECT * FROM MySQL_Clientes')
SELECT * FROM dbo.SQLServer_Clientes

here are some links that can help you

Link MySQL with SQL Server

Configuring MySQL linked server on SQL Server 2008