First of all you need to create a DSN to MS SQL Server’sPUBS database. Go to Control Panel->Administrative Tools->Data Sources.You will get a dialog as Figure A.
You can create a new DSN by clicking on the Add button andselecting the right options from the wizard.
Now connect to your MySQL server by giving some simpleinformation to SQLyog (like Hostname, Username, Password, Port etc.). Create adatabase through SQLyog by selecting DB->Create Database… or using thekeyboard shortcut Ctrl+D. Give the database name say My PUBS. After thedatabase has been created, select the Database in the left hand tree window ofSQLyog ( i.e. the object browser ) and select Tools->ODBC Import… You willget a window like Figure B.
The wizard also allows you to Import data from another MySQLData Source but since we are dealing with an ODBC source we choose Import FromOther Source… and press Next >. Remember to select the correct database inMySQL in which you want to import data in the combo dropdown. The next dialog FigureC will ask you the source DSN details. Fill up the following details in thedialog –
System/User DSN – The DSN you just created for MS SQLServer PUBS.
UserName – Username for the DSN.
Password – Password for the DSN.
and press Next.
You will get a screen like Figure D. From this screenyou can specify whether to copy all the data from the source, or you can entera SQL query to select particular records.
If you select Copy table(s) from the data source, you willbe presented with Figure E.
Clicking on the transform button (which will be availableonly if you select a table) loads up the Column Mapping and TransformationDialog as shown in Figure F.
Through this option you can change the properties of targetfields that will be imported in MySQL e.g. you can specify the data type of afield or you can say that it should be a NOT NULL field.
One powerful feature about the column mapping is that youcan specify which column you want to import in MySQL or which column you wantto fill with NULL values in the target MySQL database while importing data.
By default SQLyog will map all the columns from the sourcetable. If you want to ignore a column say phone as shown in FigureF, you can choose <ignore> option for that field in the Destinationcolumn of the GRID. An example is shown in Figure G.
Moreover if you want that a field in the Destination tableto be filled with NULL value then choose <ignore> option for thecorresponding field in the Source column of the Grid. One example is shown in FigureH.
Also you can insert same data for two or more destinationfield by choosing the same field in the source column of the GRID i.e. you canfill the First Name and the Last Name with First Name (I don’t know why youwill do that!!!).
blog comments powered by Disqus