Exporting Data from MS SQL SERVER to MySQL

Readers, always ask me to write on CURSOR, with their customize problem, But I normally write which will help
every one. One of reader asked me long back about the cursor and exporting data from MS SQL Server to MySQL.I had the same scenario, I need to export data from MS SQL to MySQL, and I have to do it quickly.
I didnt find on internet a better solution because of the time constraint.
I have acheived my task by using CURSOR and generate customize queries, So I can execute them at MySQL database and populate required data.

My main focus is to share how to use cursor and also one of the way to export data from MS SQL Server to MySQL. 

You can customize and replace your queries with one written below.

Lets Start. 

–insert records in a temp table

Select
username,
‘e50b13c57937586c4c6c6fa30f0bce24′ as ‘password’,
’33VPTHTe’ as ‘salt’,
‘dkRZvhwOMrXLwL8mSJA2PneuMkBCXD9aRLuWjgIF2grtYNW6bQ’ as ‘loginkey’,
user_email as ‘email’,
’1206339144′ as ‘regdate’,
’1206423077′ as ‘lastvisit’,
’0′ as ‘lastpost’,
snull(User_website,”) as ‘website’,
isnull(user_icq,”) as ‘icq’,
isnull(user_aim,”) as ‘aim’,
isnull(user_yim,”) as ‘yahoo’,
isnull(user_msnm,”) as ‘msn’,
isnull(user_sig,”) as ‘signature’,
isnull(user_allow_pm,’0′) as ‘receivepms’,
isnull(user_notify_pm,’0′) as ‘pmnotify’,
isnull(user_allowavatar,’0′) as ‘showavatars’,
’2′ as ‘usergroup’
into #temp
Fromphpbb_users
Where user_session_page<>0 

–Declaring variables to insert column values later
Declare @username varchar(1000)
Declare @password varchar(500)
Declare @salt varchar (250)
Declare @loginkey varchar(500)
Declare @email varchar(500)
Declare @regdate varchar(500)
Declare @lastvisit varchar(500)
Declare @lastpost varchar(500)
Declare @website varchar(500)
Declare @icq varchar(255)
Declare @aim varchar(255)
Declare @yahoo varchar(255)
Declare @msn varchar(255)
Declare @signature varchar(255)
Declare @receivepms varchar(255)
Declare @pmnotify varchar(255)
Declare @showavatars varchar(255)
Declare @usergroup varchar(10)

–declaration of the cursor
DECLARE @getBB CURSOR

–Initializing of cursor for particular query result.
SET @getBB = CURSOR FOR SELECT * FROM #TEMP

OPEN @getBB –opening a cursor
FETCH NEXT –Syntax of cursor to move next
FROM @getBB INTO – Inserting single row data as a column to variables

@username ,@password,@salt,@loginkey ,@email,@regdate,@lastvisit,@lastpost,
@website,@icq,@aim ,@yahoo,@msn,@signature,@receivepms,@pmnotify,@showavatars ,@usergroup

WHILE @@FETCH_STATUS = 0 – condition execute until record end

BEGIN 

– Here you can write your customize result or further execution.

Print ‘ INSERT INTO users(username, password, salt,loginkey,email, regdate, lastvisit,
lastpost, website, icq, aim, yahoo, msn, signature, receivepms,
pmnotify,showavatars,usergroup)VALUES

(

”’

+ @username + ”’,”’+ @password + ”’,”’+ @salt + ”’,
”’+ @loginkey + ”’,”’+ @email + ”’,”’+ @regdate + ”’,
”’+ @lastvisit + ”’,’+ @lastpost + ‘,”’+ @website + ”’,
”’+ @icq + ”’,”’+ @aim + ”’,”’+ @yahoo + ”’,”’+ @msn + ”’,
”’+ @signature + ”’,”’+ @receivepms + ”’,”’+ @pmnotify + ”’,
”’+ @showavatars +”’,”’ + @usergroup + ”’);’
 
FETCH NEXT – Moving next
FROM @getBB INTO – Insert next record to the variables.
@username ,@password,@salt,@loginkey,
@email,@regdate,@lastvisit,@lastpost,@website,@icq,
@aim ,@yahoo,@msn,@signature,@receivepms,@pmnotify,@showavatars ,@usergroup
ENDCLOSE @getBB – Closing variable
DEALLOCATE @getBB – removing from the memory
GO–droping table.

DROP TABLE #TEMP
—————————————————————————————–
Hope this will solve Export data from MS Sql Server to MySQL and also explain a cursor.

Rana

About these ads