Posted by "Richard Lynch" on 10/07/05 23:20

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

[Back to original message]