Export I leave without the name of the columns


I'm trying to export a table and I get it right but without the headers. Can you help me? This is what I have done so far

declare @sql varchar(8000)
select @sql = 'bcp "select * from Ana.dbo.detalle_incidencias" queryout C:\prueba.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
asked by anasavilu 22.05.2017 в 10:39

1 answer


As you said @Pikoh bcp has no way to export the names of the fields, however there is a "trick" that may serve you and goes through the idea of exporting on the one hand the names of the fields and the other the data itself, then a "merge" of both files, in your case it would be something like this:


-- Cabecera
SELECT  @SQL = 'BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + '','', '''') + column_name from Ana.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=''detalle_incidencias''; select @colnames;" queryout c:\cabecera.csv -c -T -S' + @@servername
exec master..xp_cmdshell @sql

-- Datos
SELECT @SQL  = 'bcp "select * from Ana.dbo.detalle_incidencias" queryout C:\datos.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

-- Merge de ambos archivos
exec master..xp_cmdshell 'copy /b "d:\cabecera.csv"+"d:\datos.csv" "d:\prueba.csv"'

-- Borramos archivos intermedios
exec master..xp_cmdshell 'del "c:\cabecera.csv"'
exec master..xp_cmdshell 'del "c:\datos.csv"'

The counter that has this method is the unnecessary additional processing to copy the two files in one, in some cases it can be important.

answered by 22.05.2017 в 15:47