SQL Server script

Question

SQL Server script

jacobe

SSC Enthusiast

Points: 195
More actions
August 13, 2003 at 6:36 am

#159839

Hello,
Is it possible to generate scripts with structure as well as data or only data from a table in SQL Server.So that I can import the data from MySql using .sql script?
Thanks
Jac

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

You can generate schema in SQL scripts from Enterprise Manager, right-clicking the database --> All Tasks --> Generate SQL Scripts. In the first tab, click Show All, then set any options you would like (pretty self-explanatory). As for the data, use DTS to export the data into a flat file. MySQL can load from a flat file, I believe (INSERT FROM FILE ... ?). Right Click Database in Enterprise Manager, select All Tasks --> Export Data. This will start up the DTS wizard and walk you through th export.

EdH334 SSCrazy Points: 2857 More actions · Answer 2

To my knowledge SQL Server does not directly support the scripting of data. However, if you need to script data from an existing table it can be done by using SELECT statements to generate an INSERT for you.

For example, if you wish to insert data into table tData, which has fields fOne int, fTwo varchar(30), fThree datetime, write the statement:

SELECT 'INSERT INTO tData (fOne, fTwo, fThree) VALUES ('+CAST(fOne AS varchar(10))+', '''+ fTwo+''', CAST('''+CAST(fThree AS varchar(21)) + ''' AS datetime))' AS InsertStatment

FROM tData

ORDER By fOne

When run in Query Analyzer this produces an output (Results in text) of:

InsertStatment                                                                                                                          

--------------------------------------------------------------------------------------------------------------------------------------- 

INSERT INTO tData (fOne, fTwo, fThree) VALUES (1, 'Two', CAST('Mar  3 2003 12:00AM' AS datetime))

…

The output can then be extracted and used in to insert the values in your SQL script file.

-Ed

Edited by - ed harling on 08/14/2003 02:57:41 AM

jacobe SSC Enthusiast Points: 195 More actions · Answer 3

Hi

I have around 100 rows in my sqlserver database. How can I easliy import to Mysql database. Is it possible to do this on a daily basis automatically from Windows 2000.

Thanks

Jac

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi Jac,

quote:
I have around 100 rows in my sqlserver database. How can I easliy import to Mysql database. Is it possible to do this on a daily basis automatically from Windows 2000.

can you spent money for a tool or do you prefer a less expensive solution?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jacobe SSC Enthusiast Points: 195 More actions · Answer 5

Hi,

I am in an in house project, so fund allocated for this. I am sorry !

Any alternative?

Thanks

Jac

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hi Jac,

quote:
I am in an in house project, so fund allocated for this. I am sorry !
Any alternative?

I think so.

If you don't want to use jpipes solution, maybe this one is an alternative.

http://www.intranet2internet.com/public/default.asp?PAGE=software&DETAILS=DUMP

It's a free programm called Access Dump, and like the name says you first must export SQL Server data to Access and then into MySQL. It's free! And maybe worth a try?

Maybe this one will also do the Access trick

http://mysqlfront.venturemedia.de/index.php?s=9503bf12672368f1fd43045383a76713&act=ST&f=2&t=328

though it is designed as a GUI front-end to MySQL

HTH

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jacobe SSC Enthusiast Points: 195 More actions · Answer 7

jacobe

SSC Enthusiast

Points: 195

August 14, 2003 at 5:36 am

#469903

Hi

Thanks a lot for the tools and appreciated.

Jac

pkeefe SSC Enthusiast Points: 107 More actions · Answer 8

You can just just use Data Transformation Services (DTS) to do this. In the SQL Server Enterprise Manager, just right click on table, all tasks, export and use the wizard ...

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Hi Jac,

well, this is just a wild guess.

Can you create Mysql as linked server in SQL Server??

BOL has an example linked server->access ODBC database

After you scripted the db structure and generated it in MySQL, maybe you can transfer the data this way?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jacobe SSC Enthusiast Points: 195 More actions · Answer 10

I am using SQLFront tool for creating and managing databses in localhost.

However when I try to connect using SQL Front tool to connect to my MYSql database in my webserver, I could not connect to the database. At the taskbar of SQL Front it shows connected, but the login screen remains and it's not responding, after some time it gives lost connection while quering.

Any solutions for this will be very appreciated?

Thanks

Jc

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

H Jac,

quote:
I am using SQLFront tool for creating and managing databses in localhost.
However when I try to connect using SQL Front tool to connect to my MYSql database in my webserver, I could not connect to the database. At the taskbar of SQL Front it shows connected, but the login screen remains and it's not responding, after some time it gives lost connection while quering.

are you speaking of the MySQLFront tool?

If so, I think you'll get a quicker answer on the MySQLFront forum here

http://mysqlfront.venturemedia.de.

HTH

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jacobe SSC Enthusiast Points: 195 More actions · Answer 12

jacobe

SSC Enthusiast

Points: 195

August 20, 2003 at 2:49 am

#470546

Hello HTH,

Many thanks for the link.

Jc

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

quote:
Hello HTH,

just call me Frank

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]