473,597 Members | 2,339 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MySQL to MS SQL Conversion

ssnaik84
149 New Member
Hi Guys,

Last year I got a chance to work with R&D team, which was working on DB scripts conversion..
Though there is migration tool available, it converts only tables and constraints..
Rest of things (stored procedures, functions).. we have to manually edit.
That time, we face some interesting challenges.. I failed to document all of them, but whatever I can share with u.. I will try.. :)


=============== =============== =============== ====

Index
-------------------------------------------------------
1. MySQL Variables
-- user variables (Global Variables)
-- local variables

2. Migration Hints
How to get
-- last inserted identity value
-- current date and time
-- declare a dummy table

3. MySQL String Functions
-- CONCAT
-- CONCAT_WS
-- GROUP_CONCAT

4. UNIQUEIDENTIFIE R

5. How to return (exit) from SP conditionally?

6. How to execute Dynamic SQL in stored procedure?

7. MySQL Cursor


=============== =============== =============== ====

1. MySQL Variables

In MySQL, there are 2 types of variables:

user variables: (Global Variables)
- symbol “@” is used to designate them.
- User variable is a variable, is working during the whole connection to the database.
- All variables for a given client connection are automatically freed when that client exits.
- User-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients
- User variable names not case sensitive in MySQL 5.0 and up.
- Syntax: SET @var_name = expr [, @var_name = expr] …
- Example: SET @t1=0, @t2=0, @t3=0;
- Note: You can also assign a value to a user variable in statements other than SET.
In this case, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements
e.g.
Expand|Select|Wrap|Line Numbers
  1. SET @t1=1, @t2=2, @t3=3;
  2. SELECT @t1,@t2,@t3; ==> output: 1,2,3
  3. SELECT @t1:=8,@t2,@t3; ==> output: 8,2,3
  4.  
local variables: (Recommended in Stored Procedure)
- The DECLARE statement is used to declare local variables.
- local variable works only within a procedure body.
- DECLARE may only be used inside a BEGIN … END compound statement and must be at its start, before any other statements.
- The scope of a variable is within the BEGIN … END block.
- Syntax: DECLARE var_name [, var_name] … type [DEFAULT value]
- Example: DECLARE l_TagID INT DEFAULT 0;
- Note: If the DEFAULT clause is missing, the initial value is NULL.

How to use local variables in MySQL:
—————————— ——-
To change Microsoft SQL Sever local variable name (@var) to MySQL local variable name, replace symbol “@” with “v_”. (select all, replace all)

Example: @userID ==> v_userID

—————————— ——-
Expand|Select|Wrap|Line Numbers
  1. MS-SQL:
  2. DECLARE @uname, @addr VARCHAR(200);
  3. SELECT @uname = uname, @addr = address FROM tbluser WHERE uid = @uid;
  4.  
  5. MySQL:
  6. DECLARE v_uname, v_addr VARCHAR(200);
  7. SELECT uname, address INTO v_uname, v_addr tbluser FROM tbluser WHERE uid = v_uid;
—————————— ——-

Example:
Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS `mytest`$$
  3. CREATE PROCEDURE `mytest`
  4. (
  5. IN v_id INT
  6. )
  7. BEGIN
  8. create temporary table temp
  9. (
  10. id int primary key auto_increment ,
  11. val int
  12. );
  13. insert into temp (val) values (22);
  14. insert into temp (val) values (50);
  15. insert into temp (id, val) values (99, 80);
  16. insert into temp (val) values (9000);
  17. begin
  18. declare v_val int default 0;
  19. select id, val into v_id, v_val from temp where id=v_id;
  20. select v_id, v_val;
  21. end;
  22. – select v_id, v_val;
  23. – (If you try to display local variables outof their scope (begin.. end), you will get error: Unknown column ‘v_val’ in ‘field list’)
  24. drop temporary table temp;
  25. END$$
  26. DELIMITER ;
  27.  
  28. try to run it with: call mytest(100)

=============== =============== =============== ====

2. Migration Hints


@@IDENTITY is also available in MySQL.

SELECT @@IDENTITY AS ‘Identity’ ;

it returns last inserted identity value.
But, better to use LAST_INSERT_ID( )

—————————— ———–
How to update a table from other

Expand|Select|Wrap|Line Numbers
  1. UPDATE JOIN …..
  2. ON . = .
  3. SET . =
  4. WHERE
—————————— ———–

MS-SQL: GetDate() ==> Use sysdate() , SELECT CURRENT_TIMESTA MP;

—————————— ———–

You are allowed to specify DUAL as a dummy table name in situations where no tables are referenced
Expand|Select|Wrap|Line Numbers
  1. SELECT 1 FROM dual
  2. WHERE left(@sSQL,2) in ('v_','d_','e_','f_','g_','h_','i_','j_')
  3.  

=============== =============== =============== ====

3. MySQL String Functions



CONCAT function:

———————
- Always use CONCAT for concatenation (to replace “+” operator in MS-SQL).
- Avoid “||” (double pipe)
- Take care of NULL value with IFNULL(column_n ame, ”); before concatenation, Otherwise, it’ll return null.

Example:
Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS `mytest`$$
  3. CREATE PROCEDURE `mytest`()
  4. BEGIN
  5. declare v_str1, v_str2, v_str3, v_str4 varchar(20);
  6. declare v_res varchar(100);
  7. set v_str1 = “abc”;
  8. set v_str2 = “pqr”;
  9. set v_str3 = “xyz”;
  10. set v_str4 = “123″;
  11. set v_res = concat(v_str1,v_str2,v_str3, v_str4); — , null);
  12. select v_res;
  13. END$$
  14. DELIMITER ;
  15. RUN: call mytest();
  16.  
CONCAT_WS function:
—————————— ——

- for Concatenate With Separator and is a special form of CONCAT().
The first argument is the separator for the rest of the arguments.
The separator is added between the strings to be concatenated

- Advantage over CONCAT: need not to check IFNULL values of each column.
It takes cares of it internally.
- Note: If the separator is NULL, the result is NULL.

Syntax: CONCAT_WS(separ ator,str1,str2, …);

Example:
Expand|Select|Wrap|Line Numbers
  1. SELECT CONCAT_WS(’,’,’First name’,’Second name’,null,’Last Name’);
  2. -> ‘First name,Second name,Last Name’
GROUP_CONCAT Function:
—————————— —————
-This function returns a string result with the concatenated non-NULL values from a group.
- USE: To concatenate multiple MySQL rows into one field

Example:
————-
Expand|Select|Wrap|Line Numbers
  1. if
  2. select col_name from temp_table;
  3. ==>
  4. col_name
  5. ———-
  6. ‘abc’
  7. ‘xyz’
  8. ‘pqr’
  9.  
  10. declare v_str varchar(100);
  11. select group_concat( col_name separator ‘*’) into v_str from temp_table;
  12. select v_str;
  13.  
  14. output: ‘abc*xyz*pqr’
  15.  
  16.  

Why to avoid “||” double pipe for concatenation
——————————————— ———–

double pipe can be used for concatenation, but, it is only legal while the sql mode is ansi.

You can tell mysqld to run in ANSI mode with the –ansi startup option.
mysqld –transacti on-isolation=SERIA LIZABLE –sql-mode=ANSI
or,
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL sql_mode = ‘ANSI’;

=============== =============== =============== ====

4. How to replace UNIQUEIDENTIFIE R

UNIQUEIDENTIFIE R is not a data type in MySQL.
We can use VARCHAR(50), as both generate GUID.

MS-SQL:
——————-
NEWID()
output: D747AE09-5E4C-44BB-BCAE-DD40EDD7747B

MySQL :
——————-
UUID()
output: 28c93603-0752-102c-ab9e-93e0a9ae6d17

Example:
—————————— ——–
Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS `mytest`$$
  3. CREATE PROCEDURE `mytest`()
  4. BEGIN
  5. DECLARE id VARCHAR(50);
  6. SET id = UUID();
  7. SELECT id;
  8. END$$
  9. DELIMITER ;
  10. run it with: call mytest()
  11.  
=============== =============== =============== ====

5. How to return (exit) from SP conditionally?
——————————————— ——
There is no alternative keyword for “return” in SP of MS-SQL.
Use “LEAVE” instead.
Put extra BEGIN & END with a label (say main)
It’s also used, for exiting through a loop.

Example:
Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS `mytest`$$
  3. CREATE PROCEDURE `mytest`()
  4. BEGIN
  5. MAIN: BEGIN
  6. SELECT ‘HI’;
  7. IF 1=1 THEN
  8. SELECT ‘ABC’;
  9. LEAVE MAIN;
  10. END IF;
  11. SELECT ‘BYE’;
  12. END MAIN;
  13. END$$
  14. DELIMITER ;
  15.  
  16. To Run: call mytest()
  17.  
=============== =============== =============== ====

6. How to execute Dynamic SQL in stored procedure

step 1. CONCATE it.
——————————————— ————-
CONCATE sql strings & store it into User Variable

SET @dynamic_sql = CONCATE(”SELECT * FROM mytable” , ” WHERE userid=”, v_uid);

step 2. PREPARE it.
——————————————— ————-
To execute dynamic sql, you need to prepare it first.

PREPARE complete_sql FROM @dynamic_sql;

Note: PREPARE statement does not work with local variables (e.g. v_sql).
It is explicitly designed to work with user variables and constant strings only.

step 3. EXECUTE it.
——————————————— ————-
EXECUTE complete_sql;

step 4. DEALLOCATE it.
——————————————— ————-
DEALLOCATE the prepared statement.

DEALLOCATE PREPARE complete_sql;


=============== =============== =============== ====

7. MySQL Cursor

A cursor has following main steps:

1) DECLARE cursor-name CURSOR FOR SELECT …;
2) OPEN cursor-name;
3) FETCH cursor-name INTO variable [, variable];
4) CLOSE cursor-name;

——————————————— ————————
Example:
Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS `newTest`$$
  3. CREATE PROCEDURE `newTest`()
  4. BEGIN
  5. create temporary table temp
  6. (
  7. id int primary key auto_increment ,
  8. val int
  9. );
  10. insert into temp (val) values (60);
  11. insert into temp (val) values (60);
  12. insert into temp (id, val) values (99, 80);
  13. insert into temp (val) values (9000);
  14. BEGIN
  15. DECLARE colID, colVAL, handle INT;
  16. DECLARE mycur CURSOR FOR SELECT id, val FROM temp; -- declare cursor
  17. DECLARE CONTINUE HANDLER FOR NOT FOUND SET handle = 1; -- declare handle
  18. OPEN mycur; -- open cursor
  19. FETCH mycur INTO colID, colVAL; -- fetch first row values from cursor
  20. REPEAT
  21. SELECT colID, colVAL; -- your code
  22. FETCH mycur INTO colID, colVAL; -- fetch values from cursor
  23. UNTIL handle = 1
  24. END REPEAT;
  25. CLOSE mycur; -- close cursor
  26. END;
  27. drop temporary table temp;
  28. END$$
  29. DELIMITER ;
  30.  
  31. call newTest();
  32.  
Oct 11 '09 #1
1 9563
wizardry
201 New Member
not to get off topic, better choice is mysql server!
Nov 11 '09 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
32413
by: Ben | last post by:
I would like to use php to query a database and retrieve a unix timestamp. The problem is that mysql is storing the data in the date format and not a timestamp. I am sure that I can amend my query to format the date returned as a timestamp without having to do the conversion in php. Can someone tell me what to put in my db query?
3
2412
by: Paul M | last post by:
I encountered the following error when trying to perform a SQL UPDATE to a MySQL database table from Python. I would apprciate any assistance. In the Python code I have tried integer and decimal format specifiers in addition to the string specifier and nothing worked. Traceback (most recent call last): File "e:\my_python_scripts\commercecraft.py", line 36, in ? cursor.execute ("UPDATE product SET price = '%s' WHERE competitorID=1
0
3515
by: Lenz Grimmer | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, MySQL 4.0.14, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites.
133
8968
by: jonathan | last post by:
hey all, I realize that this question might pop up from time to time, but I haven't seen it a while and things might of changed, so - Right now (July 2004) how does mysql stand up in comparison to oracle? We are seriously considering migrating our multi-processor oracle system to mysql to save on licensing costs, and would need several features that mysql may or may not have:
17
2880
by: chicha | last post by:
Hey people, I have to convert MS Access 2000 database into mysql database, the whole thing being part of this project I'm doing for one of my faculty classes. My professor somehow presumed I knew db's and gave me long list of things to do with that particular database, first thing being that particular conversion. Truth is that I don't know a first thing about db's, let alone using mysql... I downloaded mysql form www.mysql.com and...
9
2290
by: laredotornado | last post by:
Hello, I am tasked with converting an MsAccess db to a MySQL 4 db in a Linux environment. Can anyone recommend any good freeware/scripts to help me do this? Thanks, - Dave
0
1431
by: aman1973 | last post by:
Hi there, I've a complete source code developed using PHP 4.3 and PostgreSQL 7.4. My question is what is the best practice and how to convert the above database to MySQL? 1st reason for conversion is due to PostgreSQL database hosting company's contract termination. 2nd bcoz of the easier installation and configuration using MySQL. Please advice on this matter. Tx Aman
3
1616
by: mantrid | last post by:
Hello I have date and time in my mysql table in the form 2007-05-03 00:00:00 which I have dispayed on my webpage using echo $selldatetime I want to know how I can display it in the form 03-05-2007 Thanks
3
2318
by: Ethan Furman | last post by:
len wrote: I've never had the (mis?)fortune to work with COBOL -- what are the files like? Fixed format, or something like a dBase III style? I
0
8381
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8040
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8259
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
5847
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
3889
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3932
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2408
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1495
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1243
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.