Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2004-09-07
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


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 2000 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

636 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