Copying Data from SQL Server to MySQL

In my last post, I showed you a way to copy the structure of an SQL Server table into a new MySQL table. In this post, I will show you how you can fairly easily copy the data from that SQL Server table into your new MySQL table.

Before we start, we should make sure we have a complete list of the columns in our MySQL table (which should, if we did everything right in building the table, be identical to the columns in our MS SQL table).

Using PHP to Copy an SQL Server Table to MySQL

At work, we have a scheduled task run every day that pulls information out of a huge Oracle database and dumps it into a Microsoft SQL table on one of our local IIS Web servers. We then have a script on our remote LAMPP Web server (the one we use to serve up our public Web site) that queries that MSSQL table and displays results on the Web site.

Unfortunately, because the Web server and the MSSQL server are in different locations, the results are sometimes slow and unreliable. For instance, if, for some reason, the network connection to our IIS server is down or if the IIS server itself is down, we can’t pull any data from the MSSQL table.

The other day, I began working on a script that will pull only the information we’ll need for any given day out of the MSSQL table and then create a new table in our MySQL installation that resides on the same server as our Web site.

Following are some good tips for you if you decide to do the same thing.

Paginating Results with MS SQL

mainpgimageIn my last post, I showed you just how simple it is to retrieve paginated results using MySQL. Unfortunately, MS SQL doesn’t utilize the LIMIT command and doesn’t implement any easy way to retrieve paginated results. Instead, you actually have to run three nested queries with MS SQL in order to retrieve a specific subset of results. MS SQL supports a clause called “TOP” rather than “LIMIT” and the “TOP” clause only accepts a single parameter (the number of results to retrieve).

To retrieve paginated results with MS SQL, you will need to write the same query three times, reversing the ORDER BY clause in each query. You also have to use a little simple math in your queries.