sql server dts: Exporting Tables and Data to CD -- sql server dts

Exporting Tables and Data to CD

Gary August
12/19/2003 5:53:29 AM

sql server dts: I am running SQL Server 2000 Developer edition ver 0120.1
July 2003 on a stand alone PC, not connected to a server
or network. I have created a number of tables in a new
database. I need to export the table definitions and the
data in them so that I can install them on another machine
running SQL Server Enterprise edition in another city. I
used the Import/Export Wizard to export the tables and
data to flat files, however the field data types are not
preserved for the tables (all the field types and lengths
are char with a length of 255) when the tables are
recreated. I also tried to use DTS to export database
objects, however DTS will only allow you to do this from
server to server. How can I export the tables and preserve
the field types and lengths so when I import them to a
database on another machine the table definitions will be

Re: Exporting Tables and Data to CD

Allan Mitchell
12/19/2003 2:10:15 PM

Script them. You can either

1. Right click on DB | All Tasks | Generate SQL Script
2. Right click on Table | Copy. You can now paste the definition into a
text file
3. Use SQL DMO
4. Use a format File and BCP.

You ccan extract the data using BCP quite easily or use SQLDMO.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

[quoted text, click to view]