• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

loading MySQL data into SQL Server 2005 Express

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
zorba111
Asked:
zorba111
  • 11
  • 8
6 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
zorba111Author Commented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
zorba111Author Commented:
>> 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
 
zorba111Author Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
zorba111Author Commented:
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
 
zorba111Author Commented:
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
 
zorba111Author Commented:
should I translate the MySQL datatype blob to the SQLServer datatype varbinary(MAX)
or is there a better match ?

many thanks!!
0
 
zorba111Author Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You are correct..

MySQL datatype blob is exactly equivalent to SQLServer datatype varbinary(MAX) and hence it is the appropriate conversion too.
0
 
zorba111Author Commented:
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
 
zorba111Author Commented:
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
 
zorba111Author Commented:
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
 
zorba111Author Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now