Bases de datos

MySQL

Migración de base de datos MySQL a SQL Server

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

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á.

SET SESSION group_concat_max_len = 100000000;
 
select XSQL 
from (
		-- COMANDOS SQL PARA CREACIÓN DE TABLAS
		select 10 as ROW_BLOCK, concat('create table ',
				'[', z.TABLE_NAME, ']',
					' (', 
						GROUP_CONCAT(z.MSSQL_COLUMN_SCRIPT order by ORDINAL_POSITION separator ', '),
						case when y.PK_COLS is null then '' else concat(' primary key (', y.PK_COLS, ')') end,
						');'
				) as XSQL					
		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
 
			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
 
			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;