Solved

loading MySQL data into SQL Server 2005 Express

Posted on 2009-05-15
19
392 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
  • 11
  • 8
19 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:zorba111
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now