Generate the mysql insert script of sql server tables to migrate data

We can easily migrate the data from sql server to mysql by generating the insert statement for mysql by using this stored procedure:

/*

EXECUTE [uspSqlServerToMysqlInsertScript] 'SELECT * FROM tblDemo','tblDemo'

*/

CREATE PROC [dbo].[uspSqlServerToMysqlInsertScript](

@vcSqlQuery AS NVARCHAR(MAX),

@vcTableName AS VARCHAR(500),

@vcDestinationTableName AS VARCHAR(500) = NULL,

@btIncludeIdentity AS BIT = 0

)

AS

BEGIN

DECLARE @vcInsertQuery AS VARCHAR(MAX)

DECLARE @vcInsertTable AS VARCHAR(MAX)

DECLARE @vcInsertColumn AS VARCHAR(MAX)

DECLARE @vcQuery AS NVARCHAR(MAX)

DECLARE @vcIdentityColumn AS VARCHAR(200)

BEGIN TRY

--Deleting the table ##tblTempQuery if it is already present

IF OBJECT_ID('tempdb..##tblTempQuery') IS NOT NULL

DROP TABLE ##tblTempQuery

--Preparing query to insert query data into ##tblTempQuery

SET @vcSqlQuery = STUFF(@vcSqlQuery,

CHARINDEX(' FROM ',

@vcSqlQuery),

0,

' INTO ##tblTempQuery ')

--Finding out is there any identity column present in this table or not.

IF @vcDestinationTableName IS NULL OR @btIncludeIdentity = 0 BEGIN

IF OBJECT_ID(@vcTableName) IS NULL

RAISERROR('Invalid table name',16,1)

SELECT @vcIdentityColumn = [name]

FROM sys.columns

WHERE [Object_Id] = OBJECT_ID(@vcTableName)

AND is_identity = 1

END

--Setting destination table

IF @vcDestinationTableName IS NULL

SET @vcDestinationTableName = @vcTableName

--Inserting record from query to ##tblTempQuery

EXECUTE SP_EXECUTESQL @vcSqlQuery

--Preparing query to generate insert statement

SELECT

@vcInsertTable = ISNULL(@vcInsertTable + ',','') + '`' + name + '`',

@vcInsertColumn = ISNULL(@vcInsertColumn + ' + '','' + ','') +

'CASE WHEN ' + QUOTENAME(name) + ' IS NULL THEN ''NULL'' ELSE ' +

CASE

WHEN user_type_id IN(48,52,56,59,60,62,104,106,108,122,127,34,165,173)

THEN ''

ELSE ''''''''' + '

END +

CASE

WHEN user_type_id IN(34)

THEN '''0x'' + ' + 'CONVERT(VARCHAR(MAX),CAST(' + QUOTENAME(name) + ' AS VARBINARY(MAX)),2)'

WHEN user_type_id IN(165,173,189)

THEN '''0x'' + ' + 'CONVERT(VARCHAR(MAX),' + QUOTENAME(name) + ',2)'

WHEN user_type_id IN(189)

THEN '''0x'' + ' + 'CONVERT(VARCHAR(MAX),CONVERT(BINARY(8),' + QUOTENAME(name) + ',2),2)'

WHEN user_type_id IN(241,60)

THEN 'REPLACE((CONVERT(NVARCHAR(MAX),' + QUOTENAME(name)+ ')),'''''''','''''''''''')'

ELSE 'REPLACE((CONVERT(NVARCHAR(MAX),' + QUOTENAME(name)+ ',120)),'''''''','''''''''''')'

END +

CASE

WHEN user_type_id IN(48,52,56,59,60,62,104,106,108,122,127,34,165,173)

THEN ''

ELSE ' + '''''''''

END +

' END '

FROM tempdb.sys.columns

WHERE [object_id] = OBJECT_ID('tempdb..##tblTempQuery')

AND ((@vcIdentityColumn IS NULL) OR ((@vcIdentityColumn IS NOT NULL AND name <> @vcIdentityColumn)) )

AND user_type_id NOT IN(129,189)

SET @vcInsertTable = '''INSERT INTO ' + @vcDestinationTableName + '(' + @vcInsertTable + ') VALUES('' + '

SET @vcInsertTable = @vcInsertTable + @vcInsertColumn + '+ '');'''

SET @vcQuery = 'SELECT ' + @vcInsertTable + ' FROM ##tblTempQuery'

--Generating insert script

EXECUTE(@vcQuery)

--Droping the the table ##tblTempQuery

IF OBJECT_ID('tempdb..##tblTempQuery') IS NOT NULL

DROP TABLE ##tblTempQuery

END TRY

BEGIN CATCH

SELECT

ERROR_MESSAGE() AS ErrMsg,

ERROR_LINE() AS ErrLine

END CATCH

END