Creating a text file from a sqlserver 6.5 table with a specific sort order

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)

Creating a text file from a sqlserver 6.5 table with a specific sort order

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-10 : 09:19:55

Nicola writes "dear SqlTeam,

I'm writing a TSQL stored procedure (in sqlserver 6.5) that need to export data from a table into an external text file.

The table contains a first column that defines the sort order and a second one with the text that need to be exported. The output file must contain only the second column.

BCP (6.5) do not support SELECT...ORDER BY statement in place of the table name

The only way a see is to use a cursor with a loop like:

exec xp_cmdshell 'echo ' + @myLine + '>> myfile.txt'

but I suppose there are better ways. Any Idea?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-10 : 09:32:26

eeeeeeeeeeeeeeewwwwwwwwwwwwwwwwwwww cursors!

Take a look at isql, it has a flag to specify a column separator if you need one, and it will run any query or .SQL file you like (so you can use the ORDER BY clause) You can redirect the output to a text file, and you should get the equivalent of bcp.

It's documented in Books Online, I don't have the 6.5 version, but the 2000 version indicates it's definitely possible.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-10 : 11:18:05

This may help, and should work on any version:
1. SELECT.. INTO tmpTABLE FROM origTABLE ORDER BY x
2. exec xp_cmdshell 'BCP tmpTABLE'
3. DROP TABLE tmpTABLE

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-10 : 11:24:49

Or you could do it YellowBug's way...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources