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.
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.


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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now