Jun 24, 2013

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


No comments:

Post a Comment