export entries (sql-data - not structure) to an sql-script

Posted on 2004-09-07
Last Modified: 2012-06-21
hello experts,
i am looking for a quick and simple way to export data (not structure) from a sql-server-table into a textfile. my mainproblem is to get entries from table A of server S_A into table A of server S_B. any solution would be great.

especially i'm looking for a way to get the entries from one table into a textfile. so i'd have the possibility to insert them later into another table. in the mysql-world there is a tool named phpmyadmin which automatically generates you nice little inserts that you can use directly without modifying. i could write myself an sql-statement like following, but better would be a dynamic solution that is not that time-consuming.

(Type, value, description)
Values (''' + Replace(Type, '''', '''''') + ''', ''' + Replace(value, '''', '''''') + ''', ''' + Replace(description, '''', '''''') + ''')'

thanks in advance.
Question by:kolpdc
  • 3
  • 3

Expert Comment

ID: 11996099
Have you seen SQL Server's "Data Transformation Services (DTS)"? Check it out in Enterprise Manager. With this you can move your data from one table to another, across databases and to and from files of various types including text files. Once you have designed your export, you can save it as a DTS package for future use.

Author Comment

ID: 11996232
i am working with the enterprisemanager almost every day. not that often with dts. to me the dts does not show directly how to get the job done. i only used the dts to "copy" complete tables directly with structure and data. but this works only in an initial state.

i see several ways that could perhaps point to the solution:
1.) on export use destination txt/csv -> no solution
2.) use a select-statement to get the data -> (if i do not miss something) if i have to write my own select, i do not need dts

sorry, but i do not see, how to get it done.

Expert Comment

ID: 11996430
To get data directly from a table in one database to a similar table in another, you add two database connections into your package, one for each database. Then create a "Transform Data Task" from the Task menu, click on the source connection and then on the destination connection. This will join your two connections up with an arrow representing the task. Double-click this arrow to edit it's properties and choose your source table on the "Source" tab and destination table on the "Destination" tab. If you need to modify any of the data as part of the process you can use the "Transformations" tab, but for a straightforward copy between tables of the same structure you don't need to worry about that. Then just click OK to save the properties and click on the "Execute" button.

If you want to copy the data to a text file as in your original question, just use "Text File (Destination)" as your second connection instead of another database. This will guide you through the selection of a file name and file format. Then create a "Transform Data Task" from the Task menu and execute the package as before. This will give you your text file. If you later want to load this into another database, simply setup your source connection as the text file and your destination as the target database.

Let me know if you get stuck.
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Author Comment

ID: 11997089
got stuck...
do you mean the import/export wizard delivered with sql-server 2000? i do not have any menus to use. i'm only able using the wizards given steps (choose source, choose destination, choose action (copy tables, own select-statement, copy data between servers).

i tried several versions. one of them did not lead me into copying the entries of one table to another similar table, but in copying all tables + data. all of this only because of not unchecking a checkbox.

other version gave me a textfile that looked kind of a csv-file but without separators. but unfortunately i do not like eventually-couldbe-data thrown to another database, but 1:1 the original data - best with possibility to limit the data with certain criteria.

somehow i do not like working with the dts. it does not show me without big harasses how to get the job done. nor it seems to offer real control to me what to get in what way. i think, i like nice little (somewhat terminating) sql-statements that always work the same way - independent of applicationupgrades for example. the best case from my point of view would be a tool like the good old phpmyadmin, that lets me select which tables to export and if structure and/or data should be exported. then i have a collection of all my databases and tables - and if i like also data - in the form of scripts that enables be to restore everything exactly the same way it was before. the only thing to do is to execute one little script.

do you eventually know a good tutorial for the import/export wizard that brings me back believe in it? else this packaged wizard should be forgotten for me and i'm in need of other possibilities (scripts, tools, whatever). sorry:/.

Accepted Solution

ChrisThornton earned 500 total points
ID: 11997280
OK, if you don't like DTS that's fine. If you do decide to take another look at it later, you can access the tool directly through Enterprise Manager as follows:

- Make sure you can see your Console Tree (which is the frame on the left-side of the window). It can be woken up using the "Show/Hide Console Tree" button on the toolbar.

- Expand your server and you should see "Data Transformation Services" just under "Databases" in the tree. Click on that to expand it.

- Right-click on "Local Packages" and select "New Package" and then you get your design window with various connection types etc. on the left hand side.

Alternatively, I just responded to another question about outputting data to a file from SQL - I don't know whether it is of any interest:

Author Comment

ID: 11997854
you gave me back believe ;). there is more than the annoying little wizard - you'll only have to find it if you did not hear about it before.

yes! you're right - forget about the wizard - your last posting really solves my problem. this version gives me the possibility to do what i like to do! very helpful information. thanks you a lot!

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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