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.
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access sql to sql server express 10 32
Linked Server Issue with SQL2012 3 24
sql server tables from access 18 17
Help in Bulk Insert 9 30
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 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