bcp Export to CSV with headers

I’m trying to find a way to export all data from a table in to a csv file with a header. I’m not concerned at this point with dynamic headers (that would be nice but at this point I’m not picky). I would just like to find a way to programmatically drop a table to a csv file.

use tempdb

go

create view vw_csvexport

as

select

'"' + convert(varchar(50), Name) + '"',

'"' + convert(varchar(50), ID) + '"'

from TestHeaderTable

union all

SELECT top 100 percent

Name = '"' + name + '"',

ID = '"' + convert(varchar(50), ID) + '"'

FROM TestDataTable

go

DECLARE @.bcpCommand varchar(2000)

SET @.bcpCommand = 'bcp tempdb..vw_export out

c:\test002.txt -c -T -S SERVER'

EXEC master..xp_cmdshell @.bcpCommand

I get this error when I run this query

Create View or Function failed because no column name was specified for column 1.

If I’m going about this the completely wrong direction let me know so I can get heading the right direction to get this done.

Try the following...

xp_cmdshell 'osql /S<ServerName> /E /Q"set nocount on select top 10 Convert(Varchar(50),name) as name, id from sysobjects" /oc:\test.txt'

|||Hi,

For your view try this and you will be alright:

create view vw_csvexport

as

select

'"' + convert(varchar(50), Name) + '"' as vwNAME,

'"' + convert(varchar(50), ID) + '"' as vwID

from TestHeaderTable

union all

SELECT top 100 percent

Name = '"' + name + '"',

ID = '"' + convert(varchar(50), ID) + '"'

FROM TestDataTable

go

Actually in the view your returning table should always have specific column name, and since you had not specify any, you had the error.

Cheers