Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Executing SQL Script file in SQL Server 2005 created export file from MySQL

Posted on 2011-10-19
8
Medium Priority
?
366 Views
Last Modified: 2012-06-16
I'm in the process of transferring data from MySQL and its end destination is desired to rest in SQL Server 2005.

Using phpMyAdmin interface i can export my tables from MySQL database. I've successfully export my tables and generated SQL Script files using the mentioned interface within MySQL. Then i imported these SQL Script file into my SQL Server 2005 database and using the query Analyzer tried to run this scripts and load this data. For unknown reasons the query is failing to run.

Here is a Snippet of the code which is failing:

INSERT INTO `messages` (`ID`, `project`, `title`, `text`, `tags`, `posted`, `[user]`, `username`, `replyto`, `milestone`) VALUES
(11, 4, 'Martinelli v. Quaye', 'The arbitration hearing has been continued to December 17, 2010, at 10:30 a.m.', '', '1291734120', 2, 'Brandon Johnson', 0, 0),
(20, 6, 'Robert McDivitt''s Sect.: Donna Shilling', '', '', '1293032429', 2, 'Brandon Johnson', 0, 0),
(1146, 9, 'Arbitration Results', 'I appeared for an arbitration today 10/18/11 in Essex County. The solo arbitrator was John Scollo, Esq.. He found 100% liability on your insured which is not surprisung as this was a rear end hit situation.<br />Damages we set at $15,500 which included $5207 in documented out of pocket wage loss and $3,324 in unreimbursed medicals and $7,000 for pain and suffering.<br />The companion case ( Fogler)was also arbitrated and damages were set at $23,000 for that claim. Therefore since the two combined claims are over our remaining policy limits we will have to file a trial de novo.', '', '1318970705', 4, 'LuAnn Watson', 0, 0),
(1147, 8, 'Arbitration results', 'I appeared for an arbitration today, 10/18/11 in Essex County. The solo arbitrator was John Scollo, Esq. 100% liability was placed on us in this rear end hit situation.<br />Damages were set at $23,000 which included an undocumentated net wage loss of $2,000. Approx $9,000 was paid by PIP for Fogler''s meds so no medicals were included in the award.<br />The companion case (Basaran) was arbitrated at the same time. An award of $15,500 was placed on that matter. Since the two awards together amount to more than our remaining policy limits, this arbitration award will need to be de noved.<br />Once a trial de novo is filed&nbsp;you should expect to receive a trial date w/in 45 days.', '', '1318971147', 4, 'LuAnn Watson', 0, 0);


I did in fact create a table in SQL Server 2005 named 'messages' with the appropriate fields referenced above.

The error message is :
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '`'.


Not too descriptive. And appears to not like the marks surrounding each of the field names in the Insert Statement. Leads me to believe this is a syntax error, but not familiar enough with this script to know the correct syntax..

Your kind help is much appreciated.


0
Comment
Question by:SchmidtZ28
  • 3
  • 3
6 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 36995111
Remove all the quotes from below line and try

INSERT INTO `messages` (`ID`, `project`, `title`, `text`, `tags`, `posted`, `[user]`, `username`, `replyto`, `milestone`) VALUES
0
 

Author Comment

by:SchmidtZ28
ID: 36995201
I just discovered the following:

The following query run successfully and insert 1 row of data:
INSERT INTO messages (ID, project, title, text, tags, posted, [user], username, replyto, milestone) VALUES
(11, 4, 'Martinelli v. Quaye', 'The arbitration hearing has been continued to December 17, 2010, at 10:30 a.m.', '', '1291734120', 2, 'Brandon Johnson', 0, 0)

The following query fails:
INSERT INTO messages (ID, project, title, text, tags, posted, [user], username, replyto, milestone) VALUES
(11, 4, 'Martinelli v. Quaye', 'The arbitration hearing has been continued to December 17, 2010, at 10:30 a.m.', '', '1291734120', 2, 'Brandon Johnson', 0, 0),
(20, 6, 'Robert McDivitt''s Sect.: Donna Shilling', '', '', '1293032429', 2, 'Brandon Johnson', 0, 0)


Any idea why?
0
 
LVL 25

Accepted Solution

by:
TempDBA earned 2000 total points
ID: 36995246
I missed the Zone of the question. Its because multiple inserts with Row Constructor works only from sql server 2008. Before that, it was not suppported.
So I am afraid you have to insert single single value or you can make use of union all operator.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:SchmidtZ28
ID: 36995342
So there's no alternative?? Server Sql 2005 can not read a SQL Script file with multiple rows of data being inserted, thats seems crazy.

There must be another way i can get this data from MYSQL to SQL Server 2005. There's several thousand rows of data, so inserting them one at a time is really not an option and upgrading to 2008 is not either just for a simple import matter.

Any other ideas to get MySQL tables to SQL Server 2005?

MySQL interfaces allows for me to export the data in the following formats:
CodeGen
CSV
CSV from MS Excel
Microsoft Word 2000
JSON
LaTex
MediaWiki Table
Open Document Spreadsheet
Open Document Text
PDF
PHP Array
SQL
Texy! Text
XML
YAML

I thought the SQL option was most appropriate for getting the data to Sql Server 2005. i also tried the Excel, but the data is losing is correct column formatting on some rows of data due in part to the way the data is stored in MySql and likely the formatting of the data for the website. so i do not think the Excel is a good option.


Thanks
Appreciate your help.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 36995410
you can get the data using import data option in sql server. It builts SSIS package and uses it to fetch the data from the source.
0
 

Author Comment

by:SchmidtZ28
ID: 36995454
OK, i've used the import/export features before, but the data source was always a excel file, Access mdb, or another SQL Server table.   Never MQsql data

The first selection in SQL Server Import and Export Wizard:
Select the source from which to copy data?

Data Source?
Server Name?
Database?

How would i make these selections, i hope from your guidance i can get this data in.

What is the data source for importing this data. can i use the SQL Script files i generated?

thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

577 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