Translate Microsoft SQL (MSSQL) To MySQL

Posted April 15, 2008 at 2:43 PM by Ben Nadel

Tags: SQL

Reader Comments

Funny but today i started to look into ways to export tables/data/stored procedures/etc from MS SQL to MySQL as well in my try to make 1ssBlog work with MySQL as well. :)

This is good. It'll be my own reference, too. Like a public google.com/notebook.

Temporary Tables in MySQL5: http://dev.mysql.com/doc/refman/5.0/en/create-table.html

@David,

I am not sure that that create table page is what I am talking about. It looks like that creates an actual table (with create permissions required). I am talking about just a table-variable (like an INT, or a FLOAT, but a table). I think, but could be dead wrong, that these are two different concepts.

Ben, it's a fantastic idea to gather a "translation" list of different terms between MS-SQL and mySQL. I've always wondered whether you could ever put something like that into code so that you could create dynamic queries which would be translated into either format... but, my guess would be that the respective syntaxes are so different as to make that implausible.

@Tom,

Yeah, this is not so much going to be a programmatic translation as it is just collection of parallel examples in both syntaxes (if that can be done).

IIRC, O'Reilly's "SQL in a Nutshell" covers a lot of the syntax differences between databases.

http://www.oreilly.com/catalog/sqlnut2/?CMP=AFC-ak_book&ATT=SQL+in+a+Nutshell

re: Running multiple statements in one query

How to Combine Multiple SQL Statements in One CFQUERY
http://www.sumoc.com/blog/index.cfm/2005/12/30/MySQL-How-to-Combine-Multiple-SQL-Statements-in-One-CFQUERY

re: Getting the previous created Identity value

SELECT LAST_INSERT_ID()

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

re: Creating column aliases

Same as anywhere else

SELECT someColumn AS myColumn

FWIW, I know one of the guys on the MySQL documentation team. I can track down virtually anything. :)

One thing I wish translated well between the two:

MSSQL's TOP command and MySQL's LIMIT command.

@Dan,

Yeah, TOP and LIMIT are placed at opposite ends of the query. I wish that MS-SQL had LIMIT's ability to specify a starting place in the resultset, since it makes pagination so easy.

I don't think MySQL has a direct equivalent for MSSQL's table-valued variables. (These were new in SQL Server 2000, and they aren't a widely-implemented feature, as far as I know).

Update/Delete queries using joins -

MSSQL:

UPDATE a
SET foo = 'bar'
FROM a JOIN b ON a.baz = b.baz

MySQL:

UPDATE a JOIN b ON a.baz = b.baz
SET foo = 'bar'

Running multiple statements in one query batch: In addition to making the driver change that is mentioned in the post linked by @Adrian, in MySQL, they must be seperated by semicolons.

IF / ELSE / END logic - Do you mean in a stored procedure? The syntax is slightly different, but I don't know of any huge differences. I haven't used stored procedures in MySQL too much yet.

Transactions - biggest issue is you must be using a transaction table type. Then you use START TRANSACTION instead of BEGIN TRANSACTION. COMMIT and ROLLBACK work as in SQL Server.

Creating column aliases - I'm curious how you've found this to be different form MSSQL-- to my eye they are similar.

@Adrian,

I think you just became my new best friend :)

@davidcl,

"Creating column aliases - I'm curious how you've found this to be different form MSSQL-- to my eye they are similar."

I don't recall exactly, but I am pretty sure I ran into problems in MySQL that it didn't like this:

SELECT
( 3 ) AS temp

The parens were messing it up or something. I think I had to dumb it down to:

SELECT
3 AS temp

But, that could be wrong.

Select 3 as temp is correct for aliasing in MySQL

Also you can do select "randy" as firstname

-Randy

I tested SELECT (3) as temp and that worked fine, so I'm still wondering what the difference is.

@Randy,

I know aliasing works, but I think the parens were causing an error.

@davidcl,

I could be wrong. I will test it shortly.

Here is a nice summary of MS SQL temporary tables and table variable types. They are different.

http://www.sqlteam.com/article/temporary-tables

#tblname are local temp tables

##tblname are global temp tables

@tblname are variable tables - exist only in memory and do not need to be dropped

Yep. I did some more searching on this and it doesn't look like there's an equivalent to @table variables in MySQL. The best recommendations I've found are:

Create temporary tables using the "memory" storage engine, to provide the speed advantages of memory storage.

Use the "create temporary table" privilege to separate low-privilege users from those who can create permanent tables.

Thanks guys, I am going to be looking into this stuff at lunch, hopefully.

@ Tom, Dan

Using maxRows in the cfquery tag itself totally eliminates the need for TOP or LIMIT.

@Will, I strongly disagree. TOP and LIMIT place the limit on the sets returned by the database server. MAXROWS simply limits how much of the result Cold Fusion processes. If the unlimited query returns lots of rows, this can be a HUGE performance difference.

@david

It actually ain't that bad. :)

As blogged by Ben himself.

http://www.bennadel.com/blog/464-ColdFusion-CFQuery-MaxRows-Not-A-Bad-Compromise-When-TOP-Not-Available.htm

@Will, even a factor of 4 would be enough for me to recommend against MAXROWS. But I've rewritten code where the change from MAXROWS to TOP resulted in an improvement of a factor of 100.

One factor is how many rows are in the query.

Another factor is how the query optimizer handles any joins in the query. In some cases the database engine may be able to apply the TOP earlier in the join process, reducing the number of rows to be joined, which can have a great impact on performance.

In general-- the only place I would use MaxRows is in a query of queries. Use the database's limiting mechanism instead. (As an aside, it would be great if Coldfusion would implement MAXROWS by translating it to the appropriate language in the underlying SQL implementation).

For the "drop table if exists", you can use the INFORMATION_SCHEMA schema that is supported in both MSSQL and MySQL:

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 't_actress') ...

I actually came across a recommendation in an article somewhere in Microsoft's own documentation that advised using INFORMATION_SCHEMA instead of sys_objects for several reasons:

* it's portable (it's part of the SQL-92 standard, supported by MySQL, MSSQL, and PostGreSQL, but not Oracle -- but neither is sys_objects in Oracle, so there you go)
* it's more intuitive ("TABLES" and "TABLE_NAME" is much more explicit and self-documenting than "type='U'" and OBJECT_ID())
* the sys_* tables are internal to SQL Server and not guaranteed not to change with service packs/new versions (your code may break on a different version of SQL)

Of course, it's hard to argue all of this when MS's own tools generate code based on the sys_objects table. :/

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment