| Author |
Topic |
|
georkost
Starting Member
4 Posts |
 Posted - 2007-04-26 : 08:49:29
|
| Hello. I am really new to sql server. I have a db in the server that I use for testing. i want to export it in plain sql so as to import it in another db system. How can I do it?thank you a lot. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-04-26 : 08:58:16
|
| Do you want to script database schema only or the table data as well?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-04-26 : 09:14:50
|
| For scripting database schema only, you can use Generate Scripts option in EM/SSMS for SQL 2000/SQL 2005 respectively. Also you may need to tweak the script a bit to change the order of execution to avoid some of the errors occurring due to the table dependencies.For scripting the data in the table, try this:[url]http://vyaskn.tripod.com/code/generate_inserts.txt[/url]But it will be slower than other traditional data import tools available like DTS, BCP etc.Also check this discussion:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73608&SearchTerms=data,script[/url]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
georkost
Starting Member
4 Posts |
Posted - 2007-05-04 : 08:37:45
|
| Thank you for your answers. What I want is to export the whole db (schema and data, where is possible) into one sql script. This script should be plain sql and not t-sql (ms sql) which is icompatible with plain sql. This means that I wanted also to convert some t-sql types that don't exist in plain sql to compitible types, for example nvarchar (t-sql) to varchar , or something liuke that.Do I want too much? |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-05-04 : 08:41:34
|
| Are you using 2000 or 2005Drew"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-04 : 08:51:24
|
| "Do I want too much?"Probably! My expectation is that you will have to make hand-changes for things that don't work on your target database. For example, on Oracle you are probably going to have to change the VARCHAR statements to VARCHAR2, but I don't know of any other database that requires that!There are tools, such as Erwin, that will connect to one database, "reverse engineer" the database and then generate a script to run on a different brand of database. Visio has some database diagramming tools which may be able to do that too.Once you have built an empty database you can transfer the data out of SQL Server using DTS - that will be much easier than creating Insert statements (and will transfer the data MUCH quicker too, especially if there are lots of rows of data)Kristen |
|
|
|
georkost
Starting Member
4 Posts |
Posted - 2007-05-04 : 08:52:03
|
| I use 2005. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-04 : 09:20:12
|
| What's another db system? Another sql2k5 server or other rdbms? |
|
|
|
georkost
Starting Member
4 Posts |
Posted - 2007-05-04 : 10:05:14
|
| Another rdbms.It's valentina db. This can be a standalone dbms and it doesn't need to be client-server model. I want it for developing a multimedia title with adobe director.i think I 'll export only the data and built a script for importing them to the valentina database |
|
|
|
|
Generate Db script in plain sql