create 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 2008 Forums

Other SQL Server 2008 Topics

create text file

Author

Topic

helpme
Posting Yak Master

141 Posts

Posted - 2009-11-06 : 16:46:43

what is the easiest way to create a delimited (not comma) text file in sql 2008? In oracle, I would just do something like the following and spool it to my local drive.

select column1, ';', column2, ';' from table1

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-06 : 17:12:57

No you wouldn't..what? you would spool out the data?

Not if it's a large volume you wouldn't/couldn't

Plus, you still would have tabs in it I think

In Oracle you would do

SELECT '"'||Col1||'"'
||';"'||Col2||'"'
||';"'||Col3||'"'
ect

In SQL Server

SELECT '"'+Col1+'"'
+';"'+Col2+'"'
+';"'+Col3+'"'
ect

And if the Columns wasn't a char datatype in SQL Server (I think you don't have to do this in Oracle...although it's been awhile)

You need to do

+';"'+CONVERT(varchar(25),numCol4)+'"'
+';"'+CONVERT(varchar(25),dteCol5,103)+'"'

I think in Oracle you still also have to worry about nulls, in which case..you'll need to add

+';"'+COALESCE(Col6,'')+'"'

And if you have non varchar data, you might have to trim the data

+';"'+COALESCE(RTRIM(Col6),'')+'"'

HTH

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources