Ms sql to mysql 1

[ushtabalakh]

Hi there
I have a ms sql connection that I define this way

$dsn = "Driver={SQL Server};Server=$server;Database=$database;UID=$username;PWD=$password;";
$link = odbc_connect($dsn, $username, $password);

I also have a my sql connection that I define this way:

$myLink = mysql_connect($myServer, $myUsername, myPassword);

I was wondering if it's possible to directly select all contents of a table from ms sql server and insert all the data to a table with identical structure in mysql

thanks

 

[vacunita]

If your PHP page has access to both databases then yes.

Just use the normal mssql functions to retrieve your data, and then create a loop to insert them into mysql.

For example:

[green]\\mssql functions: connect and retrieve your data from the database.[/green]

$results=mssql_query("SELECT *FROM mytablename");

[green]\\connect to mysql database[/green]

while($row=mssql_fetch_row($results)){
[green]\\build insert statement per row[/green]

$sql="INSERT into mysqltablename VALUES('$row[0]','$row[1]',$row[2]...);
[green]\\each number corresponds to a column or field in your table [/green]
[green]\\Execute the query to insert the row[/green]
 $res=mysql_query($sql);
}

I noticed you've been asking many related questions in the forums here. I suspect you want to migrate from a mssql database to mysql. Perhaps you should be looking into exporting the contents of your DB into files (comma separated files,) that you can then easily import into mysql.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[ushtabalakh]

thanks for your response,

1- Doing it in a loop is the last option that I would use, I wish there was a way that you can assign all the data of a table into one variable, and then insert them into another database without having to enumerate the database.

I've got about 13,000 records, I think it's gonna take a long time to execute all those records

2- I can't do that because this is not a one time job, I have to write a code that does this everyday

 

[webdev007]

Could you as per vacunita export it as a CSV
and run a daily update cron?
plus if the 13K (ACCESS) records are not all updated
then in access add a row to mark the one updated
and have the cron moving only the updated records

 

[jpadie]

13000 rows is not that big. obviously the time taken will depend on the number of columns and the datatypes.

assuming you're not storing blobs, i'd guess the execution time will be only a couple of seconds. you, of course, keep the first recordset open whilst the cursor is moving through the rows

you can gain some speed by using PDO and prepared statements for the write to mysql.

 

[ushtabalakh]

This is the way I'm currently doing it

while (($line = odbc_fetch_array($result, $i))) { 

	$values = "'".join("', '", $line)."'";
	$myQuery = "INSERT INTO temptable VALUES('1', ".featQuery($myLine)."'0', $values)";
	$myResult = mysql_query($myQuery, $myLink);
}

featquery turnes values of the array suitable for insert command.

It takes around 10 minutes to insert all the rows. Is there any way to make it faster?

can I use PDO to make it faster? can you give me a small example?

I guess collecting 500 insert statements and then inserting them at once would make it faster, I'm gonna try that.

 

[jpadie]

why are you not using native mssql calls?

what does the featQuery() function look like? is there inefficiency in that?

for a pdo based insert

//connect to mysql

$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'username';
$pwd = 'password';

$mypdo = new PDO ($dsn, $username, $password);

//prepare the query
$query = "Insert into template values ('1', " . featQuery($myLine) . "'0', ?, ?, ?, ?)"; //one question mark for each field
$statement = $pdo->prepare($query);

//iterate the mssql recordset
while (($line = odbc_fetch_array($result, $i))) { 
    //pass the $line array of values through to the statement
   $statement->execute($line);
}

 

[ushtabalakh]

>why are you not using native mssql calls?
I'm not sure where they can be used in my case, can you please elaborate?

>what does the featQuery() function look like? is there inefficiency in that?

It's a function written by someone else, I don't think it's slow, here is how it looks like:

function featQuery ($line) {
	$query = "";
	for ($i=1;$i<=AGENTMAX;$i++) {
		$query .= "'".$line['featured$i']."', ";
	}
	return $query;
}

Thanks A LOT for the pdo example, is it significantly faster than the way I have used it?

 

[jpadie]

it should be significantly faster.

i'm not sure what the $myline variable looks like so it's difficult to comment on the function. it seems odd to use a defined term though. i would also want to escape the contents of $myline before using it in the query.