On Apr 9, 7:27 am, "Designing Solutions WD"
<michael.grass...@gmail.comwrote:
Hello,
I have some questions on my options available.
I have to export some tables to csv files to enable another department
to process the files. What I need is a way to do this in ms sql
though a stored proc with quoted identifiers and column names as
heads. I cannot figure out how to do this.
Can anybody give me some options that would be the best options.
I am using ms sql 2000.
Thank you for your time.
Straight forward solution is to UNION field names with data and use
BCP -
1. Create a SELECT statement that includes field names -
DECLARE @names varchar(100), @delimiter varchar(10)
SET @delimiter = ','
SELECT @names = COALESCE(@names + @delimiter, '') + '"' + name + '"'
FROM syscolumns where id = (select id from sysobjects where
name='TABLE_TO_EXPORT')
SELECT 'select ' + @names
2. Concatenate it with UNION SELECT cast(FIELD1 as char), cast(FIELD2
as char), .... From TABLE_TO_EXPORT (which is ugly but it has to be
done to create union)
3. Then using UNION create a VIEW which can be used in BCP to export
data
4. Use BCP from command shell xp_cmdshell "BCP ""select * from
VIEW_TO_EXPORT"" out c:\results.csv -c -t, -T -S<servername>
- Roman