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!!!).