Solved

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

Posted on 2004-09-07
6
369 Views
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.

-------------------------
Select
'Insert Into [ENV_SYSTEMCONFIG]
(Type, value, description)
Values (''' + Replace(Type, '''', '''''') + ''', ''' + Replace(value, '''', '''''') + ''', ''' + Replace(description, '''', '''''') + ''')'
From [ENV_SYSTEMCONFIG]
-------------------------

thanks in advance.
0
Comment
Question by:kolpdc
  • 3
  • 3
6 Comments
 
LVL 2

Expert Comment

by:ChrisThornton
Comment Utility
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.
0
 
LVL 4

Author Comment

by:kolpdc
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:ChrisThornton
Comment Utility
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.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 4

Author Comment

by:kolpdc
Comment Utility
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:/.
0
 
LVL 2

Accepted Solution

by:
ChrisThornton earned 500 total points
Comment Utility
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:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21121316.html
0
 
LVL 4

Author Comment

by:kolpdc
Comment Utility
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!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

10 Experts available now in Live!

Get 1:1 Help Now