Convert Mssql Script To Mysql

0 views

Skip to first unread message

Ronald

unread,

Aug 3, 2024, 4:16:44 PM8/3/24

to onlyterib

It took a week of negotiations to receive such a file so I'm afraid to ask mySQL administrators to send me MS SQL SERVER compatible sql. Is there a way to convert mySQL dialect to MSSQL SERVER dialect automatically ?

SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures and functions, triggers, queries, embedded SQL statements and SQL scripts from MySQL to Microsoft SQL Server.

The easiest method would be to obtain a converter. There are multiple options available, but It might be difficult to find a good one: a lot of spammy options are available, all costing money ofcourse.

Adding a short exerpt:
If you check out the part called 'Using Data Loading'. The export file you allready have is probably fine, so you can go and skip 'Generating mysqldump Data Extract Scripts'.

Using the extracted scripts with SQL Query Analyzer
Generated scripts can now be used to create database objects and insert data. The preferred method to construct a database schema from MySQL scripts is to use the SQL Query Analyzer tool that is included with SQL Server 2000.
You can run SQL Query Analyzer directly from the Start menu, or from inside SQL Server Enterprise Manager. You can also run SQL Query Analyzer from the command prompt by executing the isqlw utility.
In order for the script to correctly execute there is some additional work required, which involves certain changes with the SQL dialect. Also, remember to walk through the SQL script and change the data types to SQL Server compatible types. The diagram below shows an imported script from mysqldump, it is important to note that the dump is an ASCII script file.

Also, MS-SQL doesn't use the ` (backtick) character, so you would need to get rid of all of them. However, some of the field/table names currently surrounded by backticks might be reserved words, in which case, you need to delimit those fields/tables with [fld_name] with brackets.

You could (re)create the MySQL database from the MySQL scripts you received and use a database conversion tool. I have used FullConvert in the past and works excellent. Supports many databases and is very fast!

Paste the script into the box below and click 'convert'. This runs a series of replacements on the script converting the syntax between MS SQL and MySQL. You may well need to make further changes to the script (we've only added them as we've needed them), you can add extra replacements below - if you think things you have added might be of use to others or you'd like us to add something but aren't sure how to do it please get in touch: to...@burrist.co.uk.

To create and convert a database script: Go to the 'Tables' page for your database in MS SQL Enterprise Manager, select all the tables, right click, select 'All Tasks'->'Generate SQL Script'. In the formating tab, remove 'Generate the DROP command' and make sure 'Generate the CREATE command' is on. On the options tab, switch off all the 'Security Scripting Options', in the 'Table Scripting Options' you can select indexes and keys - this tool doesn't deal with triggers or full text indexes at the moment. Save or Preview the script and paste it below. Click 'convert' to convert the MS SQL script to MySQL. Copy and paste the MySQL script from the box at the bottom into a suitable program (e.g. MySQL Query Browser, PhpMyAdmin etc), create or select the database and run the script.

If there are errors when you run the script - first of all check whether they are really causing you any trouble - there may be 'multiple primary keys' errors that don't affect the creation of the database. If you can see how to fix the errors in the script edit the script manually or add a replacement below and run the conversion again - let us know when you add something that might be useful to others. See our products page for our other useful database tools, for viewing and querying databases, automatic merging of remote SQL and dbf databases and for transforming XML data into reports.

The replacements are a series of Regular Expressions in the form ReplaceWithText@RegularExpression that are executed in order. You can edit the expressions here. If you need the expression to repeat until no further changes are made (make them 'recursive') add an extra @ at the end of the line. Let us know when you add something that might be useful to others.

;@GOnumeric@\[numeric\]blob@\[image\]datetime@\[smalldatetime\]decimal@\[decimal\]decimal(12,2)@\[money\]decimal(12,2)@\[smallmoney\]tinyint@\[bit\]int@\[smallint\]double@\[float\]varchar@\[char\]varchar@\[varchar\]varchar@\[nvarchar\]datetime@\[datetime\]text@\[text\]int@\[int\]@ ON \[PRIMARY\]@ TEXTIMAGE_ON \[PRIMARY\]@\[dbo\]\.@ALTER TABLE [^;]* DEFAULT [^;]*.`@\[`@\]FOREIGN KEY @(`[^`]+`) FOREIGN KEY;@(ALTER TABLE `[^`]+` ADD)([^,;]+ FOREIGN KEY [^,;]+),@@COLLATE [^ ]+.AUTO_INCREMENT@IDENTITY\([^)]+.AUTO_INCREMENT@IDENTITY \([^)]+.@WITH \([^)]+.@WITH\([^)]+.@ALTER TABLE [^;]* NOCHECK [^;]*.@ NOT FOR REPLICATION@ CLUSTERED@SET ANSI_NULLS [^;]*.@SET QUOTED_IDENTIFIER [^;]*.@SET ANSI_PADDING [^;]*.

I'm writing a patch for the application, part of which involves executing a 5000 line sql script to make modifications to the database. The script is written using MSSQL syntax, however I was wondering if there is an application for automatically converting MSSQL syntax to that of Oracle and/or MySQL to save me from having to manually do it.

And even more concerning is that the databases support different locking mechanisms, so you may be able to get some tables and indexes in there, but you might introduce some issues that are very difficult to debug/replicate when you expose it into the wild.

You need as 3 experts to sort that lot out - one each of SQL Server, MySQL and Oracle. That or your database needs to run with a maximum of one user, with poor performance and very average database mechanisms.

I don't know of a database engine programming syntax converter. However, since you're working on a multi-database platform, I would consider using Enterprise Library and its application block. This will save you headaches, you got to trust me!

Another possible way is to bring these engines as close as possible to each other in ressemblance so that you may work more conveniently with the three of them. Thus, it requires you to analyze and to decide what function should be created where and why? This is hard work, seriously.

I'm writing a patch for the application, part of which involves executing a 5000 line sql script to make modifications to the database. The script is written using MSSQL syntax, however I was wondering if there is an application to automatically converting MSSQL syntax to that of Oracle and/or MySQL to save me having to manually do it.

No, I don't believe Alteryx can be used as a script conversion tool.

Alteryx is designed to work with data, it is designed as a tool that is likely to do the same job as what the script is doing, to prepare, and transform data.

In theory you can convert it with Alteryx, but it would require yourself to build the transformation of the old script to the new script.

Ben

Hello Liquibase users, I am currently re-writing a web-based app to use MySQL instead of MS Sql Server. I have been using dbVisalizer to run custom SQL statements to output .CSV files from SQL Server for subsequent import into MySQL. This works but is completely manual:

In general it worked well, although there was some manual changes I needed to make to the generated changelog. We were actually using it to support keeping a test dataset in mysql and exporting it to sqlserver, so it was a process we would run periodically, so we were able to script the required changelog changes.

Recently I've inherited a project build in MSSQL that needs to be converting into MySQL. For anyone who has looked into this you'll quickly realise its not a simple conversion. There are a few tools you can try and also a manual way.

I truncate the new table so I can run the script as needed. Next I select all records from a table using the $old connection, loop over the data and insert into the MySQL database using the $new connection.

The MSSQL-to-MySQL converter works great, it's easy to setup assuming both databases are on the same machine. You can convert directly into a MySQL database or to a SQL file. Either specifing specific tables or all tables. The trail version is limited to 50 records for a complete conversion you will need to buy the product. At $49 is a bargain will save hours of work, I highly recommend this approach.

RazorSQL includes a database conversion tool that can convert one or many MS SQL Server tables to MySQL. The conversion tool can be accessed via the following menu option in RazorSQL:

DB Tools -> Database Conversion

Inside the Database Conversion menu there are options for converting one table or to convert a selection of tables withina specific schema or database.

Information needed on the screen includes the existing MS SQL Server table to convert, the name of the new MySQL table to create,and whether to execute the conversion directly on a MySQL database connection or whether to generate an SQL script file with theDDL and SQL insert statements for the conversion.

After clicking the convert button, RazorSQL will generate a MySQL specific create table statement for the MS SQL Server table. It will alsogenerate an SQL insert statement for each row in the MS SQL Server table that can be executed against the new MySQL table.Listed below is an example SQL script that would be generated by the RazorSQL MS SQL Server to MySQL conversion tool.

When electing to convert multiple tables, in addition to the create table statement and SQL insert statements, RazorSQL cangenerate alter table statements to set up foreign key relationships. Listed below is an example MS SQL Server to MySQL conversionscript that includes the alter table statement to create the foreign key on the Project table that references the Department table.