Home > mysql, Tips > Migration from MySQL to MSSQL – Quick Tip

Migration from MySQL to MSSQL – Quick Tip

February 18th, 2010 Leave a comment Go to comments

With the help of some colleagues, I could export all the indexes and primary keys, which we were using from a MySQL table as MSSQL compatiable TSQL using a union query and on the MySQL information_schema.

I did the migration to MSSQL using some tips found online, and calling a sp after connecting directly with the MySQL server. Actually forgot how it was done. Anyway it does not matter in this post. Will add it as a comment later on. The important matter was that, the method did not import any of the indexes, or primary keys. We manually created those were absolutely necessary to roll out the project. And now that we were about to release a service patch, and this includes more incredible search methods, the indexes are absolutely necessary.

Digging through the MySQL Dump of the db, enlightned me that manually building the indexes would take about a couple of days, and we could not afford that. From old times I started to check out the structure of the information_schema in MySQL. This led to a simle but effective query to export all the indexes, which we were using in the system to be exported as a column of TSQL. Further with the use of SELECT [] INTO OUTFILE, I could get the indexes into a sql file, which could be run directly on the MsSQL Database through aqua data studio.

SELECT 
  REPLACE(create_index,'~n',"\n") INTO OUTFILE "/tmp/mkIndexes.tsql" 
FROM 
 (
    SELECT concat('CREATE INDEX [','idx_',TABLE_NAME,'_',
         CONSTRAINT_NAME,']','~n',' ON ',TABLE_NAME,'(',
         group_concat(column_name order by ordinal_position),')','~nGO') as create_index 
    FROM 
        `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` 
    WHERE `TABLE_SCHEMA` = 'db_getting_exported' and constraint_name <> 'PRIMARY' 
    group by constraint_name 
    UNION 
    SELECT concat('ALTER TABLE ',TABLE_NAME,'~n  ',
         ' ADD CONSTRAINT pk_',TABLE_NAME,'_',
         replace(group_concat(column_name order by ordinal_position),',','_'),
         ' PRIMARY KEY (',group_concat(column_name order by ordinal_position),
         ')~nGO') as create_index 
    FROM 
         `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` 
    WHERE `TABLE_SCHEMA` = 'db_getting_exported' and constraint_name = 'PRIMARY' group by TABLE_NAME
    )as x;

Please note that I have inserted line breaks for readablity. Actually, I used everything on one line, without the line breaks. And dont forget to change db_getting_exported with the actual db name which you are exporting.

With the Constraints
When some suggestions came through, I thought about revisiting the information_schema once more, and came up with this version of the query.

SELECT
   replace(cIdx, '~n', "\n")
FROM (
   SELECT concat('ALTER TABLE ',b.TABLE_NAME,
              '~n  ',' ADD CONSTRAINT uk_',b.TABLE_NAME,
              '__',replace(group_concat(b.column_name order by b.ordinal_position),
              ',','_'),' UNIQUE (',
              group_concat(b.column_name order by b.ordinal_position),
              ')~nGO') as cIdx 
   FROM 
    `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS` a, `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` b 
   WHERE 
     a.table_schema='db_getting_exported' and 
     b.table_schema='db_getting_exported' and 
     a.CONSTRAINT_NAME = b.CONSTRAINT_NAME and 
     a.constraint_type='UNIQUE' 
   GROUP BY a.TABLE_NAME
   UNION 
   SELECT concat('ALTER TABLE ',TABLE_NAME,'~n  ',
         ' ADD CONSTRAINT pk_',TABLE_NAME,'_',
         replace(group_concat(column_name order by ordinal_position),',','_'),
         ' PRIMARY KEY (',group_concat(column_name order by ordinal_position),
         ')~nGO') as cIdx
    FROM
        `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
    WHERE 
        `TABLE_SCHEMA` = 'db_getting_exported' and 
        constraint_name = 'PRIMARY' 
    GROUP BY `TABLE_NAME`
)as x

Categories: mysql, Tips Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

− three = five