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

SQL SERVER – Find any expression in the database

My colleagues asked me hundred times, how to find a particular columns, table name, stored procedure, views in the database.

Always forgot to remember one query. So for them only 🙂

Below query you can any table, stored procedure or views who ever have that expression as a column or object name.

SELECT * FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOMMENTS WHERE TEXT LIKE ‘%PRODUC%’)

The query produced all the object names. where ever it finds expression like ‘PRODUC

If you wanted to find any table name only then

SELECT * FROM SYSOBJECTS WHERE NAME LIKE ‘%TA%’ and xtype=‘u’

result

For stored procedure  pass  xtype=‘p’ and for views xtype=‘v’

Hope next time my friends will visit from my blog to get this query 😉

Rana

CLR data type ,hierarchyid

The heirarchyid is a system data type available in SQL Server 2008 to represent hierarchies. It is based on a CLR data type, but is always available, whether the CLR is enabled or not.

The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy. A column of type hierarchyid does not automatically represent a tree. It is up to the application to generate and assign hierarchyid values in such a way that the desired relationship between rows is reflected in the values.

  • /
  • /1/
  • /0.3.-7/
  • /1/3/
  • /0.1/0.2/
  • for more detail