Solved

loading MySQL data into SQL Server 2005 Express

Posted on 2009-05-15
19
397 Views
Last Modified: 2012-05-07
using phpMyAdmin's Export function I tried to export the database as a SQL script, but the script failed in SQL Server 2005 Express, even when I set the SQL compatibility mode to "MSSQL" on the phpMyAdmin Export page.

I'm using Microsoft SQL Server Management Studio Express v9.
I'm opening a New Query and dropping the SQL script in, then pressing execute.

I get the following error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'delphpjob_invoices'.

from this SQL code:


use altmorec;
 
CREATE TABLE IF NOT EXISTS "delphpjob_invoices" (
  "opid" int(10) NOT NULL default '0',
  "op_type" int(1) NOT NULL default '0',
  "compid" int(5) NOT NULL default '0',
  "pricing_type" mediumint(2) NOT NULL default '0',
  "jobs" int(3) NOT NULL default '0',
  "featuredjobs" int(3) NOT NULL default '0',
  "contacts" int(3) NOT NULL default '0',
  "1job" float(10,2) NOT NULL default '0.00',
  "1featuredjob" float(10,2) NOT NULL default '0.00',
  "1contact" float(10,2) NOT NULL default '0.00',
  "date_added" date NOT NULL default '0000-00-00',
  "info" varchar(255) NOT NULL default '',
  "currency" varchar(5) NOT NULL default '',
  "listprice" float(10,2) NOT NULL default '0.00',
  "discount" float(3,2) NOT NULL default '0.00',
  "vat" float(3,2) NOT NULL default '0.00',
  "totalprice" float(10,2) NOT NULL default '0.00',
  "paid" char(1) NOT NULL default '',
  "payment_mode" mediumint(2) NOT NULL default '0',
  "payment_date" date NOT NULL default '0000-00-00',
  "description" varchar(255) NOT NULL default '',
  "updated" char(1) NOT NULL default 'N',
  "validated" char(1) NOT NULL default 'N',
  KEY "opid" ("opid")
);

Open in new window

0
Comment
Question by:zorba111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
19 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24394366
Try this one out:

Fixed out the issues.
 
CREATE TABLE "delphpjob_invoices" (
  "opid" int(10) NOT NULL default '0',
  "op_type" int(1) NOT NULL default '0',
  "compid" int(5) NOT NULL default '0',
  "pricing_type" mediumint(2) NOT NULL default '0',
  "jobs" int(3) NOT NULL default '0',
  "featuredjobs" int(3) NOT NULL default '0',
  "contacts" int(3) NOT NULL default '0',
  "1job" float(10,2) NOT NULL default '0.00',
  "1featuredjob" float(10,2) NOT NULL default '0.00',
  "1contact" float(10,2) NOT NULL default '0.00',
  "date_added" date NOT NULL default '0000-00-00',
  "info" varchar(255) NOT NULL default '',
  "currency" varchar(5) NOT NULL default '',
  "listprice" float(10,2) NOT NULL default '0.00',
  "discount" float(3,2) NOT NULL default '0.00',
  "vat" float(3,2) NOT NULL default '0.00',
  "totalprice" float(10,2) NOT NULL default '0.00',
  "paid" char(1) NOT NULL default '',
  "payment_mode" mediumint(2) NOT NULL default '0',
  "payment_date" date NOT NULL default '0000-00-00',
  "description" varchar(255) NOT NULL default '',
  "updated" char(1) NOT NULL default 'N',
  "validated" char(1) NOT NULL default 'N',
  CONSTRAINT [opid]
    PRIMARY KEY CLUSTERED ([opid])
);

Open in new window

0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24394377
Hope your phpMyAdmin's Export function is creating scripts for MySQL and not for MSSQL. In MSSQL scripts needs to be in the structure which I mentioned earlier.
0
 

Author Comment

by:zorba111
ID: 24394385
ok, presumably "IF NOT EXISTS" is not valid T-SQL

so much for "MSSQL compatibility mode" in phpMyAdmin !!!

I re-exported, this time de-selecting the "Add IF NOT EXISTS" option in phpMyAdmin, the code got a bit further, now its choking on KEY :

Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'KEY'.

Am I going to have to go thru the whole script like this, ironing out any incompatibilities between MYSQL and SQLServer's implementation of SQL ?

or is there an easier way to do this with CSV's etc?
(mind you I couldn't see any generic import functionality with the SQLExpress tools)
-- phpMyAdmin SQL Dump
-- version 2.11.9.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 15, 2009 at 12:39 PM
-- Server version: 5.0.67
-- PHP Version: 5.2.6
 
--
-- Database: `altmorec_job`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `delphpjob_invoices`
--
 
CREATE TABLE "delphpjob_invoices" (
  "opid" int(10) NOT NULL default '0',
  "op_type" int(1) NOT NULL default '0',
  "compid" int(5) NOT NULL default '0',
  "pricing_type" mediumint(2) NOT NULL default '0',
  "jobs" int(3) NOT NULL default '0',
  "featuredjobs" int(3) NOT NULL default '0',
  "contacts" int(3) NOT NULL default '0',
  "1job" float(10,2) NOT NULL default '0.00',
  "1featuredjob" float(10,2) NOT NULL default '0.00',
  "1contact" float(10,2) NOT NULL default '0.00',
  "date_added" date NOT NULL default '0000-00-00',
  "info" varchar(255) NOT NULL default '',
  "currency" varchar(5) NOT NULL default '',
  "listprice" float(10,2) NOT NULL default '0.00',
  "discount" float(3,2) NOT NULL default '0.00',
  "vat" float(3,2) NOT NULL default '0.00',
  "totalprice" float(10,2) NOT NULL default '0.00',
  "paid" char(1) NOT NULL default '',
  "payment_mode" mediumint(2) NOT NULL default '0',
  "payment_date" date NOT NULL default '0000-00-00',
  "description" varchar(255) NOT NULL default '',
  "updated" char(1) NOT NULL default 'N',
  "validated" char(1) NOT NULL default 'N',
  KEY "opid" ("opid")
);

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24394455
Is that possible to create primary keys and foreign keys separately and not with the creation of tables, then your problem will be resolved right.
0
 

Author Comment

by:zorba111
ID: 24394506
>> Is that possible to create primary keys and foreign keys separately and not with the creation of tables, then your problem will be resolved right.

not with phpMyAdmin :-(

I tried your edits above, and SQLServer didn't like the "()" after the type definitions for the columns, so I'm going to have to go thru this by hand.

oh well !
thanks for your corrections so far

maybe you'd stick around and help me if I get stuck on something else in this script ?

then u can have the points for sure!
0
 

Author Comment

by:zorba111
ID: 24394568
the crazy thing is, even when I get phpMyAdmin to output in ANSI or TRADITIONAL mode, the "KEY" keyword and format is still generated.

either
 phpMyAdmin is not producing ANSI SQLwhen its supposed to be
or
SQL Server 2005 is not able to read ANSI SQL

so much for international standards LOL !!
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24394633
Its the first choice not the second one, because SQL Server 2005 relies on ANSI SQL standards and hence will be able to read it out.

Revert if you face any issues at some other stage.
0
 

Author Comment

by:zorba111
ID: 24394715
for my own reference, other changes needed:

int(x) -> int
mediumint -> int
float(x,y) -> float
date -> datetime
blob -> varbinary(MAX)

remove `s around column and table names
0
 

Author Comment

by:zorba111
ID: 24394742
MSSQL: how to insert multiple rows in one insert statement

so far I have:

INSERT INTO tableA (col1, col2, col3) VALUES (1, 2, 3), (1, 2, 4), (1, 2, 5);

but it doesn't like the "," between the row sets

how to seperate these ?



0
 

Author Comment

by:zorba111
ID: 24394749
should I translate the MySQL datatype blob to the SQLServer datatype varbinary(MAX)
or is there a better match ?

many thanks!!
0
 

Author Comment

by:zorba111
ID: 24394838
looking at MSSQL's INSERT syntax, I'm not sure if we can insert multi  rows in one operation!

any ideas ?

[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO] 
    { <object> | rowset_function_limited 
      [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
    }
{
    [ ( column_list ) ] 
    [ <OUTPUT Clause> ]
    { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) 
    | derived_table 
    | execute_statement 
    } 
} 
    | DEFAULT VALUES 
[; ]
 
<object> ::=
{ 
    [ server_name . database_name . schema_name . 
      | database_name .[ schema_name ] . 
      | schema_name . 
    ]
        table_or_view_name
}

Open in new window

0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24395146
For multiple inserts like this:

INSERT INTO tableA (col1, col2, col3) VALUES (1, 2, 3), (1, 2, 4), (1, 2, 5);

You have to use

INSERT INTO tableA (col1, col2, col3)
SELECT 1, 2, 3
UNION ALL
SELECT 1, 2, 4
UNION ALL
SELECT 1, 2, 5
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24395159
You are correct..

MySQL datatype blob is exactly equivalent to SQLServer datatype varbinary(MAX) and hence it is the appropriate conversion too.
0
 

Author Comment

by:zorba111
ID: 24563477
Hello experts!

Sorry for the delay, caused by needing to upgrade to SQL Server 2008 to get this script working (and then my MSDN wasn't working, needed to reinstall MSVS 2008 etc etc. blah blah)

Reason for needing SQL Server 2008 >

For multiple inserts like this:
INSERT INTO tableA (col1, col2, col3) VALUES (1, 2, 3), (1, 2, 4), (1, 2, 5);

the fix was to upgrade as described here
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24411928.html

now I should be able to run this script and award points v. soon!
0
 

Author Comment

by:zorba111
ID: 24564307
now I'm getting:

Msg 102, Level 15, State 1, Line 688
Incorrect syntax near 'auto_increment'

due to line:

CREATE TABLE "delphpjob_persons" (
  "persid" int(5) NOT NULL auto_increment,

what is the SQL Server 2008 version of auto_increment ?
0
 

Author Comment

by:zorba111
ID: 24564494
a few other edits needed:

(1)
remove ` character (i.e. "backslash-style" apostrophe) around identifiers

(2)
auto_increment -> identity

CREATE TABLE tableA (
  "colA" int(5) NOT NULL auto_increment,
  "colB" int(5) NOT NULL default '0')
) AUTO_INCREMENT=85 ;

becomes:

CREATE TABLE tableA (
  "colA" int(5) NOT NULL IDENTITY(85,1),
  "colB" int(5) NOT NULL default '0')
);

(3)

KEY | INDEX [index name] [index type] (index_col_name, ....)
->
UNIQUE (index_col_name)

e.g.

CREATE TABLE blah (
colA,
colB,
help_word,
KEY "help_word" ("help_word")
)

becomes..

CREATE TABLE blah (
colA,
colB,
help_word,
UNIQUE ("help_word")
)




0
 

Author Comment

by:zorba111
ID: 24564511
ok, this is the only one I'm left with:

CREATE TABLE "phpjob_cronjobs" (
  "cron_type" enum('jobmail','resumemail','expire') NOT NULL default 'jobmail',
  "cron_date" datetime NOT NULL default '0000-00-00 00:00:00',
  "cron_start" timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  "cron_status" enum('done','running') NOT NULL default 'done',
  "cron_priority" tinyint(1) NOT NULL default '0'
);

gives

Msg 102, Level 15, State 1, Line 867
Incorrect syntax near 'jobmail'.

how to do "enum" line in SQL Server (2008) ??
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24565902
Glad to see you resolved your earlier problems as well.
The only issue left out is your Enum and hope I have answered you over there and the options are:

1. To create a Master Lookup table with all values and creating a foreign key constraint which will validate the values for this particular column.
2. Create a Check constraint for that column to validate whether the values falls within the list specified.

First one is More Normalistic since it creates a separate entity for the enum values.
Second one is a kind of constraint specifying the values to be present in the list specified.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24565909
One more suggestion:

Since you are new to SQL Server you might be not aware of Schemas.

Create schema ur_schema_name;

would create a schema and all tables which you try to create needs to be inside this schema like

CREATE TABLE ur_shcema_name.tableA (
  "colA" int(5) NOT NULL IDENTITY(85,1),
  "colB" int(5) NOT NULL default '0')
);

which will help you handle tables efficiently if more and more users are using your database and creating their own schemas.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question