Transferring Data to MySQL Using SQLyog

Need any easy way to import data from your ODBC-compliant database into MySQL? Look no further than SQLyog’s ODBC Import tool. Read all about it inside.

With the growth of MySQL in the RDBMS market and the product being FREE, thousands of database users are migrating to MySQL, The one thing that requires attention is – How are they going to transfer their LEGACY data to MySQL?

The answer is SQLyog’s ODBC Import Tool.

{mospagebreak title=What is SQLyog?}

SQLyog is a powerful Free Windows based Front End to MySQL. You can download the latest version of SQLyog at http://www.webyog.com/sqlyog/download.html. Read the review of SQLyog by Builder.com here.

SQLyog provides ODBC Import Tool to streamline the transfer of data from any ODBC compliant data source to MySQL. In this article I will show you how to import data to MySQL. As an example I will import data from the sample PUBS database in MS SQL Server to MySQL.

{mospagebreak title=Getting Started}

First of all you need to create a DSN to MS SQL Server’s PUBS database. Go to Control Panel->Administrative Tools->Data Sources. You will get a dialog as Figure A.

Figure A

 

You can create a new DSN by clicking on the Add button and selecting the right options from the wizard.

Now connect to your MySQL server by giving some simple information to SQLyog (like Hostname, Username, Password, Port etc.). Create a database through SQLyog by selecting DB->Create Database… or using the keyboard shortcut Ctrl+D. Give the database name say My PUBS. After the database has been created, select the Database in the left hand tree window of SQLyog ( i.e. the object browser ) and select Tools->ODBC Import… You will get a window like Figure B.

Figure B

 

The wizard also allows you to Import data from another MySQL Data Source but since we are dealing with an ODBC source we choose Import From Other Source… and press Next >. Remember to select the correct database in MySQL in which you want to import data in the combo dropdown. The next dialog Figure C will ask you the source DSN details. Fill up the following details in the dialog –

System/User DSN – The DSN you just created for MS SQL Server PUBS.

UserName – Username for the DSN.

Password – Password for the DSN.

and press Next.

Figure C

 

You will get a screen like Figure D. From this screen you can specify whether to copy all the data from the source, or you can enter a SQL query to select particular records.

Figure D

 

If you select Copy table(s) from the data source, you will be presented with Figure E.

Figure E

 

Clicking on the transform button (which will be available only if you select a table) loads up the Column Mapping and Transformation Dialog as shown in Figure F.

Figure F

 

Through this option you can change the properties of target fields that will be imported in MySQL e.g. you can specify the data type of a field or you can say that it should be a NOT NULL field.

One powerful feature about the column mapping is that you can specify which column you want to import in MySQL or which column you want to fill with NULL values in the target MySQL database while importing data.

By default SQLyog will map all the columns from the source table. If you want to ignore a column say phone as shown in Figure F, you can choose <ignore> option for that field in the Destination column of the GRID. An example is shown in Figure G.

Figure G

 

Moreover if you want that a field in the Destination table to be filled with NULL value then choose <ignore> option for the corresponding field in the Source column of the Grid. One example is shown in Figure H.

Figure H

 

Also you can insert same data for two or more destination field by choosing the same field in the source column of the GRID i.e. you can fill the First Name and the Last Name with First Name (I don’t know why you will do that!!!).

{mospagebreak title=Importing only selected rows or importing the resultset of a query}

 

Choosing the option Use a query to specify the data to transfer in Figure D loads up the following dialog (Figure I)

Figure I

 

The next dialog box (Figure J) allows you to run the rocess immediately or save the data as SQL scripts for later execution.

Figure J

 

The final dialog box (Figure K) displays the result of the Import Process.

Figure K

 

If any error occurred while importing data then SQLyog will display you the errorneous query and the error message (Figure L). For your convenience, it even logs them in sqlyog.err. After checking out the error, you can either continue with the Import Process or you can stop there.

{mospagebreak title=Conclusion}

SQLyog gives you one of the most powerful ODBC import tools that I have ever encountered. Using SQLyog I was able to successfully migrate date from Oracle, MS SQL Server, Sybase, Access, Foxpro, Excel, etc. The best part is that it automatically chooses the ‘best fit’ column type for the target table / column. Most of time you just need to press Next..Next and Finish! It is over.

Google+ Comments

Google+ Comments