Export a file with headers Help

[robert030975]

Hi having a little bit of an issue and wondered if its possible to do soemthing better??


so create table new_tbl (id int, basket varchar(10), Calc numeric)

insert into new_tbl
values (1, 'Test', 123.12)
, (2, 'Test', 456.78)
, (3, 'Test', 901.12)


My question is the only way to use BCP union all to put results with headers into a file?? As I have 140 columns with will make the below statement very time consuming

exec master..xp_cmdshell
'BCP "select ''id'', ''Basket'', ''Calc'' UNION ALL SELECT cast(id as varchar(10)), Basket, cast(Calc as varchar(28)) FROM TMP.dbo.new_tbl" QUERYOUT C:\test\test.txt -T -c -t}'

One side question is it possible to change the file ie. instead of test. txt FEB14.txt?

Any help would be great !


I have SQL2008 R2 SP 1 64

 

[TheBugSlayer]

Try replacing

select ''id'', ''Basket'', ''Calc'

with

SELECT Name FROM SYS.Columns WHERE OBJECT_NAME(OBJECT_ID)) = 'new_tbl'


Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[robert030975]

Thanks its a great help , still have to convert columns on the other side but great!