Update ms sql server from mysql

[kangz]

Hi,

I have a linux server that I am using to run mysql 5 with a demographics database on it.

 

I would like to update another remote database running ms sql server (2005 i think) with the same demographic data, all this from within the mysql server (or using php script if necessary) since that is where I have control.

 

Any idea how to achieve that?

Thanks.

[Muddy_Funster]

It can be easily done using a php script, you just need to make sure that you have the relevent libraries enabled, then just run an update on the msSql from within the select loop from the MySql.

[kangz]

It can be easily done using a php script, you just need to make sure that you have the relevent libraries enabled, then just run an update on the msSql from within the select loop from the MySql.

 

I have the libraries enabled (mssql for php) and i would love to have the part where you say "..from within the select loop.." thats where i figure out i would have a problem on how to go about it.

[Muddy_Funster]

When you perform a select on the MySQL you will loop through the returned records using a WHILE loop.  Within the WHILE loop you assign the record returned to your update query and perform that there and then.  I have no idea how comfortable you are with PHP and SQL so don't know how much more you need, nor do I have any table information to use to produce any example code.  Here is some Psudo that should help:

MySQL_Select = SELECT fieldsByName FROM table
Execute MySQL_Select OR (Show Error Message)
WHILE MySQL_Select returns Rows {
MSSql_Update = UPDATE table SET (MSSqlFieldName = FieldReturnedFrom MySQL_Select) [repeat SET(...) for each field to be updated]
Execute MSSql_Update OR (Show Error Message)
}

[kangz]

When you perform a select on the MySQL you will loop through the returned records using a WHILE loop.  Within the WHILE loop you assign the record returned to your update query and perform that there and then.  I have no idea how comfortable you are with PHP and SQL so don't know how much more you need, nor do I have any table information to use to produce any example code. 

 

Thanks.

I am wondering though how you are going to update the remote server which is mssql from the php script. If you can help me with the entire code both on php and mysql i will appreciate so much. Im "intermediate level" with both.

 

You can assume the following details:

mysql server : localhost (testsvr0)

database: demographics_dbo

table: tbl_biodata (id, fname, dob, staffid)

 

mssql server: mssqlsvr0

database: employeedb

table: tbl_biodata (id, fname, dob, staffid)

 

Thanks.