Bases de datos

MySQL

Migración de base de datos MySQL a SQL Server

Última actualización: 03-11-2023 10:40

Para la migración de bases de datos MySQL a SQL Server existen herramientas de Microsoft para hacerlo.

Pueden echar una vuelta por estos enlaces:
• https://docs.microsoft.com/en-us/sql/ssma/mysql/migrating-mysql-databases-to-sql-server-azure-sql-db-mysqltosql?view=ssdt-18vs2017
• https://docs.microsoft.com/en-us/sql/ssma/mysql/migrating-mysql-data-into-sql-server-azure-sql-db-mysqltosql?view=ssdt-18vs2017
• https://formatica.es/migracion-base-datos-desde-mysql-ms-sql-server/

El tercer enlace es muy claro en el "paso a paso" de cómo hacer una migración.

Lo que aquí se comparte es cómo generar un script SQL para hacer la migración de base de datos MySQL a MSSQL sin conectar a una base de datos destino.

Ejecutando este SELECT en la base de datos MySQL que se necesita migrar se obtendrá el script en sintaxis MSSQL para crear tablas, con sus valores por omisión, llaves primarias y foráneas y los índices que existan.

¿Y los datos? El SELECT expuesto solamente genera script de estructuras. Y si, si se puede conseguir la generación de un script en formato MSSQL desde base de datos MySQL... Cuando lo haya publicado agregaré el enlace por acá.

Notas de actualización

• La versión de [15-Abr-2018 09:28] tenía un error que no era tolerado por MySQL. Cuando se había escrito el script se había hecho sobre MariaDB.
• La versión de [03-Nov-2023] se tiene confirmado que funciona sobre MySQL. = )

SET SESSION group_concat_max_len = 100000000;
 
select XSQL 
from (
		-- COMANDOS SQL PARA CREACIÓN DE TABLAS
		select www.ROW_BLOCK, concat('create table [', www.TABLE_name, ']', ' (', www.COLUMNS, www.PK, ');') as XSQL
from (
	select 10 as ROW_BLOCK, z.TABLE_NAME
			, GROUP_CONCAT(z.MSSQL_COLUMN_SCRIPT order by ORDINAL_POSITION separator ', ')  AS COLUMNS
            , case when y.PK_COLS is null then '' else concat(' primary key (', y.PK_COLS, ')') end as PK
 
 
	from (
					select a.TABLE_NAME, b.ORDINAL_POSITION, 						
							concat('[', b.COLUMN_NAME, '] ', 
								case 				
									when b.DATA_TYPE in ('varchar', 'char') then concat(b.DATA_TYPE, '(', b.CHARACTER_MAXIMUM_LENGTH, ')')
									when b.DATA_TYPE in ('varbinary', 'tinyblob', 'blob', 'mediumblob', 'longblob') 
										then concat('varbinary(', b.CHARACTER_MAXIMUM_LENGTH, ')')
									when b.DATA_TYPE = 'int' then 'int'
									when b.DATA_TYPE = 'timestamp' then 'datetime'
									when b.DATA_TYPE = 'year' then 'smallint'
									when b.DATA_TYPE = 'float' then  concat('float(', b.NUMERIC_PRECISION +  b.NUMERIC_SCALE, ')')					
									when b.DATA_TYPE in ('double', 'real') then 'float(53)'
									when b.DATA_TYPE in ('decimal') then concat('decimal(', b.NUMERIC_PRECISION, ', ',  b.NUMERIC_SCALE, ')')
									when b.DATA_TYPE in ('smallint', 'tinyint', 'date', 'bigint', 'datetime' , 'time') then b.DATA_TYPE			
									when b.DATA_TYPE in ('text', 'tinytext', 'mediumtext', 'longtext') then 'nvarchar(max)'
									else '[UNKNOW DATA TYPE]'
								end,
								case when b.EXTRA = 'auto_increment' then ' identity(1, 1)' else '' end,
								case b.IS_NULLABLE when 'YES' then '' else ' not' end,
								' null',
								case 
									when b.COLUMN_DEFAULT is null then ''
									else concat(' default ''', 
											replace(replace(b.COLUMN_DEFAULT, 'CURRENT_TIMESTAMP', 'getdate()'),
												'0000-00-00', '1753-01-01'
											),											
											'''')
								end
 
							) as MSSQL_COLUMN_SCRIPT
					from information_schema.tables a 
						inner join information_schema.columns b 
							on a.table_schema = database()
								and b.table_schema = database()
								and b.table_name = a.table_name
					where a.TABLE_TYPE = 'BASE TABLE'
					order by a.TABLE_NAME, b.ORDINAL_POSITION			
				) z
				left outer join (
						select a.TABLE_NAME,  
								GROUP_CONCAT(
												concat('[', b.COLUMN_NAME, ']') 
												order by ifnull(b.ORDINAL_POSITION, 1) 
												separator ', '
											) as PK_COLS
						from information_schema.TABLE_CONSTRAINTS a
							inner join information_schema.KEY_COLUMN_USAGE b 
								on a.table_schema = database()
									and b.table_schema = database()
									and b.table_name = a.table_name
									and b.CONSTRAINT_NAME = a.CONSTRAINT_NAME
						where a.table_schema = database() 
							and a.CONSTRAINT_TYPE = 'PRIMARY KEY'
						group by a.TABLE_NAME	
					) y on y.TABLE_NAME = z.TABLE_NAME					
	group by z.TABLE_NAME , case when y.PK_COLS is null then '' else concat(' primary key (', y.PK_COLS, ')') end
            ) www
 
			union 
 
			-- COMANDOS SQL PARA CREACIÓN DE LLAVES FORÁNEAS
			select 20 as ROW_BLOCK, concat('alter table [', a.TABLE_NAME, ']'
					' add constraint ', a.CONSTRAINT_NAME,
					' foreign key (', 
						group_concat(
							concat('[', b.COLUMN_NAME, ']') 
							order by ifnull(ORDINAL_POSITION, 0) 
							separator ', '
							), 
						')',
					' references [', b.REFERENCED_TABLE_NAME, '] (', 
						group_concat(
							concat('[', b.REFERENCED_COLUMN_NAME, ']') 
							order by ifnull(ORDINAL_POSITION, 0) 
							separator ', '
								), 
							')'
					, ';') as FK_SQL
			from information_schema.TABLE_CONSTRAINTS a
				inner join information_schema.KEY_COLUMN_USAGE b 
					on a.table_schema = database()
						and b.table_schema = database()
						and b.table_name = a.table_name
						and b.CONSTRAINT_NAME = a.CONSTRAINT_NAME
			where CONSTRAINT_TYPE = 'FOREIGN KEY'
			group by a.TABLE_NAME, a.CONSTRAINT_NAME ,  b.REFERENCED_TABLE_NAME
 
			union
 
			-- COMANDOS SQL PARA CREACIÓN DE ÍNDICES
			select 70, 	
				concat('create ',
						case a.NON_UNIQUE when 0 then ' unique' else '' end,
						' index [', a.INDEX_NAME, '] on [', a.TABLE_NAME, '] (', 
							GROUP_CONCAT(concat('[', a.COLUMN_NAME, ']') order by a.SEQ_IN_INDEX separator ', '), 
						');') as XSQL 			
			from information_schema.STATISTICS a
				left outer join information_schema.TABLE_CONSTRAINTS b 
					on b.table_schema = database() 
							and b.TABLE_NAME = a.TABLE_NAME 
							and b.CONSTRAINT_NAME = a.INDEX_NAME 
							and b.CONSTRAINT_TYPE in ('PRIMARY KEY', 'FOREIGN KEY')
			where a.table_schema = database() 	
				and b.CONSTRAINT_NAME is null
			group by a.TABLE_NAME, a.INDEX_NAME, a.NON_UNIQUE
 
		) ft 
order by ROW_BLOCK;