Re: data move from mssql to mysql via php

On Fri, October 7, 2005 8:31 am, blackwater dev wrote: > I have an app with a requirement to hit an external mssql db and move > the data to a local mysql database. What's the best way to do this? > I was thinking of querying the mssql db and writing the contents to a > flat file then using mysql load data from infile query to pump it in > but I am not sure if this is the best option so am looking for > suggestions.... If one or the other database servers allows access from other than 'localhost', you can simply have two connections open, one to each db, and XFer data in a single PHP script. <?php $mssql = mssql_connect('whatever', ...); $mysql = mysql_connect('whatever', ...); $query = 'select ... from ...."; $result = mssql_query($query, $mssql); while ($row = mssql_fetch_row($result)){ $query = "insert into ... values(" . implode("', '", $row) . ")"; mysql_query($query, $mysql); } ?> Obviously you'd need a ton of error-checking etc. This would be, perhaps, better than dump/load if you also need to do some business logic in there to determine which records have changed where, and which should or should not get re-loaded etc. Would also be handy if you need to run this script often to syncronize the two. But if it's a one-time dump, then presumably SQL Server can be convinced to dump out a flat-file that you can load into MySQL as you originally intended -- That's probably gonna be fastest/easiest for a one-time deal. -- Like Music? http://l-i-e.com/artists.htm