Skip to content

Instantly share code, notes, and snippets.

@dozheiny
Created March 30, 2022 20:04
Show Gist options
  • Save dozheiny/3d501f1a016e1c2a5a3ee5c2d598e896 to your computer and use it in GitHub Desktop.
Save dozheiny/3d501f1a016e1c2a5a3ee5c2d598e896 to your computer and use it in GitHub Desktop.
sql to mysql converter
DECLARE @RowsToProcess bigint
DECLARE @CurrentRow bigint
DECLARE @SelectTable VARCHAR(50)
IF OBJECT_ID('tempdb..#Worksheets') IS NOT NULL
DROP TABLE #Worksheets
CREATE TABLE #Worksheets (TABLE_CAT varchar(30), TABLE_SCHEM VARCHAR(30), TABLE_NAME VARCHAR(50), TABLE_TYPE VARCHAR(20), REMARKS VARCHAR(30))
INSERT INTO #Worksheets
EXEC sp_tables_ex
@table_server = 'MYSQL',
@table_catalog = 'bddarm';
DECLARE @table1 TABLE (RowID bigint not null primary key identity(1,1), col1 VARCHAR(50) )
INSERT into @table1 (col1) SELECT TABLE_NAME FROM #Worksheets
SET @RowsToProcess=@@ROWCOUNT
SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@SelectTable=col1
FROM @table1
WHERE RowID=@CurrentRow
SELECT TOP (1) @SelectTable AS WorkOnIt
FROM #Worksheets
EXECUTE('
IF OBJECT_ID(N''tempdb..#' + @SelectTable + ''') IS NOT NULL
BEGIN
DROP TABLE #' + @SelectTable + '
END
SELECT *
INTO #' + @SelectTable + '
FROM OPENQUERY(MYSQL, N''SELECT * FROM bddarm.' + @SelectTable + ''')
IF((SELECT count(*) FROM #' + @SelectTable + ')>0)
BEGIN
-- We drop in case the schema has changed at the source
DROP TABLE IF EXISTS ' + @SelectTable + ';
-- Recreate the destination table schema and data
SELECT *
INTO ' + @SelectTable + '
FROM #' + @SelectTable + '
END ')
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment