CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008
Kevin Dark
Kevin Dark

Reputation: 476

Update MySQL Database from values in MS SQL

Basically I have an MS SQL database in one location (location A) and I need to update the results of a specific SQL query in to a MySQL server in another location (location B) which is behind a VPN. The MySql server can only be accessed when connected to the VPN.

I've tried to connect to the VPN on the machine which has the MS SQL database on it (in location A), downloaded the MySql connection software to enable it to connect to a MySql database in an attempt to create a linked server, but alas, that machine when connected to the VPN cannot see the MySql database or at least fails to connect. (I know I'm using the correct security credentials).

I should probably mention that this MS SQL machine is a file server with a fixed IP address and hence fixed DNS etc. I think this may have something to do with why it's failing. I've tried adding the DNS server IP addresses (that get given out when it connects to the VPN) to it's list of available DNS servers through tcp/IP config, but still no luck.

The annoying this is that if I connect to the VPN on another computer - say my desktop machine, I can connect to both the SQL server (through SSMS) and the MySql server (through Toad for MySQL).

So I guess the question is, without writing a console app to query the SQL database and then add the results of that query in to the MySql databse, or get SQL to spit out the insert query (in text form) to then run in to the MySql database (there's 350000 rows), is there another, more tidy and clean way to achieve this? This is really a one-off exercise.

Thanks for your help in advance.

Upvotes: 2

Views: 177

Answers (1)

Yasen Zhelev
Yasen Zhelev

Reputation: 4045

Export the data from the MS SQL server in CSV file (or SQL script) and import it into the MySQL server.

If it is only specific columns that you want to update then export oly them plus the primary key (if you have one) to a CSV file and then do some text replacement in order to build a update queries out of the data.

Upvotes: 1

Related Questions