Output the Data from a select to a file

[gurupot]

Hi,

I want to write a script which executes a select statement and outputs the data to file in the format I specify. What would be the easiest way to accomplish this.

Thanks. :rolleyes:

[gurupot]

Database is SQL server.

[exterminator]

Look out for the bcp utility - http://www.sqlteam.com/item.asp?ItemID=4722

By the way, what do you mean when you say - the format is to be as specified by you?

[gurupot]

That was helpful. Thanks.
I was wondering if I could specify the output format. For example if my database table has fields like fname, lname, DOB. I would like it to output the data as 15 chars of fname, 15 chars of lname and 10 chars of DOB. So if the fname is less than 15 chars it should fill it with spaces.
So basically I would like my output to look like

012345678901234567890123456789012345678901234567890
Test1 Test2 12061975
Test3 Test4 12061976
Test5 Test6 12061977
Test7 Test8 12061978
Test9 Test10 12061979
Test11 Test12 12061980

[gurupot]

Oops. It removed the spaces in the output. Please ignore it.

[exterminator]

Try using the SPACE function to pad the fields with spaces - http://msdn2.microsoft.com/en-us/library/aa259234(SQL.80).aspx

The query there shows SPACE usage with constant value - probably you put the argument as an expressions - something like appending the field with SPACE(fixed witdth - fieldlength) i.e.

select
LTRIM(RTRIM(fname)) + SPACE(15 - LEN(LTRIM(RTRIM(fname)))),
LTRIM(RTRIM(lname)) + SPACE(15 - LEN(LTRIM(RTRIM(lname))))
from
sometable
If DOB is not a string field (char/varchar) - you might probably need to convert it to a varchar first and then append the spaces. But if it is going to be the last field in the query, I guess you shouldn't need to pad it. Depends on what you want to do. Let me know if the above works, because I can't test it (dont have SQL Server installed) but it gives an idea about how to proceed.