Is .sql file different for MySQL and SQL?

Question

I have a .sql file and I want to load it into MySQL database. I don't know from which database (MySQL or MS-SQL) it was created.

Now, I am trying to import that file into MySQL database. It is showing errors while importing and executing that file.

Q1. So, my question is whether the .sql file generated from MySQL and MS-SQL are different?

Note: I am using SQLYog software (graphical interface for MySQL) for importing the file.

Here is the ERROR:

Query: CREATE TABLE ads ( id bigint(20) NOT NULL auto_increment, city_id int(11) NOT NULL, type text collate utf8_bin NOT NULL, town text collate utf8_bin NOT NULL, address text collate utf8_bin NOT NULL, price text collate utf8_bin NOT NULL, info text collate utf8_bin NOT NULL, link text collate utf8_bin NOT NULL, hasImage int(11) NOT NULL, language varchar(2) collate utf8_bin NOT NULL, time_added varchar(255) collate utf8_bin NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1

Error occured at:2009-09-08 17:41:01 Line no.:35 Error Code: 1050 - Table 'ads' already exists

Query: CREATE TABLE ads ( id bigint(20) NOT NULL auto_increment, city_id int(11) NOT NULL, type text collate utf8_bin NOT NULL, town text collate utf8_bin NOT NULL, address text collate utf8_bin NOT NULL, price text collate utf8_bin NOT NULL, info text collate utf8_bin NOT NULL, link text collate utf8_bin NOT NULL, hasImage int(11) NOT NULL, language varchar(2) collate utf8_bin NOT NULL, time_added varchar(255) collate utf8_bin NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1

Error occured at:2009-09-08 17:41:21 Line no.:35 Error Code: 1050 - Table 'ads' already exists

Query: CREATE TABLE ads ( id bigint(20) NOT NULL auto_increment, city_id int(11) NOT NULL, type text collate utf8_bin NOT NULL, town text collate utf8_bin NOT NULL, address text collate utf8_bin NOT NULL, price text collate utf8_bin NOT NULL, info text collate utf8_bin NOT NULL, link text collate utf8_bin NOT NULL, hasImage int(11) NOT NULL, language varchar(2) collate utf8_bin NOT NULL, time_added varchar(255) collate utf8_bin NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1

Error occured at:2009-09-08 17:41:35 Line no.:35 Error Code: 1050 - Table 'ads' already exists

Query: CREATE TABLE ads ( id bigint(20) NOT NULL auto_increment, city_id int(11) NOT NULL, type text collate utf8_bin NOT NULL, town text collate utf8_bin NOT NULL, address text collate utf8_bin NOT NULL, price text collate utf8_bin NOT NULL, info text collate utf8_bin NOT NULL, link text collate utf8_bin NOT NULL, hasImage int(11) NOT NULL, language varchar(2) collate utf8_bin NOT NULL, time_added varchar(255) collate utf8_bin NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1

Error occured at:2009-09-08 17:42:07 Line no.:35 Error Code: 1050 - Table 'ads' already exists

Query: 1 Stanford University 6700 http://www.orkut.co.in . . .

Answer 1

It depends on which software exported this file. Also check in the file's header which software did it and analyze the syntax to determine where this file belongs to. Note also that SQL is microsoft sql server.

Answer 2

The file extension ".sql" is essentially meaningless: it's just there so that you know what the file is. It will just be a plain text file containing SQL, which you can open in Notepad. Therefore, there's no special "mysql" or "sql server" extensions.

The errors you're getting there "Table 'ads' already exists" are because you're trying to create a table which already exists in the database. (Did you read the error?) You have a few options:

1. Change the SQL to this:

   CREATE TABLE IF NOT EXISTS ads ( id bigint(20) ...
   

2. Change the SQL to this:

   DROP TABLE IF EXISTS ads;
   CREATE TABLE ads (id bigint(20) ...
   

3. Clear out all the tables in the DB first.

Answer 3

MySQL and Microsoft SQL Server do, unfortunately, implement different dialects of SQL. So, the answer to your question :

Q1. So, my question is whether the .sql file generated from MySQL and MS-SQL are different?

is "yes, quite possiby". However, the specific error you're showing appears to be due strictly to a "Create table" statement on line 35 being executed over and over and over again, and that can't depend on such dialect differences, so the causes have to be other ones (such as bugs or subtle format differences in what that "SQLYog" program, which I'm not familiar with, expects as its input).

Answer 4

Yes. SQL varies greatly from database to database. While there is a SQL standard which most databases support much of, every database has numerous, incompatible nonstandard features and functions above and beyond that specified by the standard.

There are also many migration docs avaiable:

MSSQL => MySQL: http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

MySQL => MSSQL: http://technet.microsoft.com/en-us/library/cc966396.aspx

There are two obvious things in your CREATE TABLE statement that MSSQL doesn't support which jump out immediately:

ENGINE=InnoDB
AUTO_INCREMENT=1

Those are both MySQL-specific statements.

Answer 5

It looks like your script is being executed multiple times (as the line number where the error occurs is the same each time). The error also indicates that you're trying to define a table that already exists, which may be from the first time this script ran. You may want to look into how the script is being run to see why it may be executing multiple times.

The SQL itself looks fine for MySQL. It wouldn't run with SQL Server.