DATA EXPORT TO TEXT FILE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DATA EXPORT TO TEXT FILE

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-10 : 23:21:38

HI,
I want to write an script for exporting my data from table to text file with tab delimated and use first row as column name. can anyone help me

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-10 : 23:33:19

Have you used DTS before? DTS stands for data transformation services. It can do tab delimited and first row with column names. To check out DTS, you can right click on your database and go to all tasks then export data. This will bring you to a wizard. If you are familiar with DTS, just bring up the DTS designer and start setting up the text file and SQL Server connections, then connect them together with a transformation.

BTW, you can save the package when going through the wizard so that you can easily rerun it. Doing this will also allow you to check out how a DTS package is done.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-10 : 23:44:04

thank
Yes ran itby using wizard and save it too,but i need is to write my own and help in how to white, the save package soenn't show anything except for connection1(SQL server) and connection2(textfile).
Can u plz help me

Thanks Again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-10 : 23:46:41

That's exactly what you are supposed to see. Now double click on the connections to see the settings. It's the text file settings that you'd be most interested in. Also, double click on the line that connects the two connections. That is your transformation.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-10 : 23:59:47

thanks
Yes I saw it but again can I write my own script which contains all the connection infos and query and exports infos and deploy it on SQL server
do u know how to write or this any help or web site
Thanks again

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-11 : 05:08:54

Create a global temp table with a single column. format the data to be exported into that table then use bcp to export.

Less flexible is something like

declare @sql varchar(1000)
select @sql = 'bcp "select name from mydb..syscolumns where id = object_id(''mytbl'')" queryout c:\mytbl.txt -c'
exec (@sql)
select @sql = 'bcp mydb..mytbl out c:\a.txt -c'
exec (@sql)
select @sql = 'type c:\a.txt >> c:\mytbl.txt'
exec master..xp_cmdshell @sql
select @sql = 'del c:\a.txt'
exec master..xp_cmdshell @sql

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-11 : 12:22:26

AsimKhaliq, you can write DTS packages in VB. But you can also just go to the DTS designer to do the work. You might want to pick up a book on DTS to get you started.

But nr's (Nigel's) solution will work for you as well.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources