Export text with bcp including column names 1

[psychodad71]

Is there any way to export data from a table to a text file including column names ?

This is my first attempt:

exec master..xp_cmdshell 'bcp "select * from table" queryout c:\test.txt -c -t; '

 

[JayKusch]

--1) create view
CREATE VIEW v_BcpOutMyTableWithColumnNames
AS

SELECT TOP 100 PERCENT Col01, Col02, Col03...
FROM (
SELECT 1 seq,
'Col01' Col01, 'Col02' Col02, 'Col03' Col03, ...
UNION ALL
SELECT 2 seq,
cast(Col01 as varchar(100)),
cast(Col02 as varchar(100)),
cast(Col03 as varchar(100)),...)
FROM db_name..table_name
) x

--2)bcp out results from this view
EXEC master..xp_cmdshell 'bcp db_name..v_BcpOutMyTableWithColumnNames out destination_path\FileExtract.txt -Sserver_name -Uuser_name -Ppassword -c'



Thanks

J. Kusch

 

[john76]

I'll post an alternate method, but I like the view method better, for reasons you'll soon see.

1. Create a temp table of just the field names, such as:
SELECT field1 = 'field 1 name',
field2 = 'field 2 name'
INTO tempdb..FieldNames_TableName

2. bcp both the field name table and the actual table out to separate files, say fieldnames.bcp and tabledata.bcp

3. Use windows copy command to merge the two files:
copy fieldnames.bcp + tabledata.bcp newtable.txt

The disadvantage of this method is that if you have a large data table, you are copying the file twice (once for bcp, once for copy command) which doubles your processing time.

As such, I'd use the view method ( I'm going to from now on! )

Thanks JayKusch!

John