sql-server - ejemplos - ejecutar bcp desde sql server

exportar tabla a archivo con encabezados de columna(nombres de columna) utilizando la utilidad bcp y SQL Server 2008 (13)

He visto una serie de ataques para intentar que la utilidad bcp exporte los nombres de las columnas junto con los datos. Si todo lo que hago es arrojar una tabla a un archivo de texto, ¿cuál es el método más sencillo para que bcp agregue los encabezados de las columnas?

Aquí está el comando bcp que estoy usando actualmente:

bcp myschema.dbo.myTableout myTable.csv /SmyServer01 /c /t, -T

Además de la solución de marc_s, también puedes usar sqlcmd o sqlcmd

Esto incluye encabezados y puede actuar como bcp usando -Q y -o. Sin embargo, no son compatibles con archivos de formato como bcp.

Aquí hay un procedimiento almacenado bastante simple que también hace el truco ...

CREATE PROCEDURE GetBCPTable @table_name varchar(200) AS BEGIN DECLARE @raw_sql nvarchar(3000) DECLARE @columnHeader VARCHAR(8000) SELECT @columnHeader = COALESCE(@columnHeader+'','' ,'''')+ ''''''''+column_name +'''''''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name DECLARE @ColumnList VARCHAR(8000) SELECT @ColumnList = COALESCE(@ColumnList+'','' ,'''')+ ''CAST(''+column_name +'' AS VARCHAR)'' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name SELECT @raw_sql = ''SELECT ''+ @columnHeader +'' UNION ALL SELECT '' + @ColumnList + '' FROM '' + @table_name --PRINT @raw_SQL EXECUTE sp_executesql @raw_sql END GO

Debería poder resolver este problema con una vista cte y un archivo por lotes que contenga el código bcp. Primero crea la vista. Dado que es relativamente sencillo, no creé una tabla temporal, normalmente lo hago

CREATE VIEW [dbo].[vwxMySAMPLE_EXTRACT_COLUMNS] AS WITH MYBCP_CTE (COLUMN_NM, ORD_POS, TXT) AS ( SELECT COLUMN_NAME , ORDINAL_POSITION , CAST(COLUMN_NAME AS VARCHAR(MAX)) FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE TABLE_NAME = ''xMySAMPLE_EXTRACT_NEW'' AND ORDINAL_POSITION = 1 UNION ALL SELECT V.COLUMN_NAME , V.ORDINAL_POSITION , CAST(C.TXT + ''|'' + V.COLUMN_NAME AS VARCHAR(MAX)) FROM [INFORMATION_SCHEMA].[COLUMNS] V INNER JOIN MYBCP_CTE C ON V.ORDINAL_POSITION = C.ORD_POS+1 AND V.ORDINAL_POSITION > 1 WHERE TABLE_NAME = ''xMySAMPLE_EXTRACT_NEW'' ) SELECT CC.TXT FROM MYBCP_CTE CC INNER JOIN ( SELECT MAX(ORD_POS) AS MX_CNT FROM MYBCP_CTE C ) SC ON CC.ORD_POS = SC.MX_CNT

Ahora, crea el archivo por lotes. Creé esto en mi directorio Temp pero soy flojo.

cd/ CD "C:/Program Files/Microsoft SQL Server/110/Tools/Binn" set buildhour=%time: =0% set buildDate=%DATE:~4,10% set backupfiledate=%buildDate:~6,4%%buildDate:~0,2%%buildDate:~3,2%%time:~0,2%%time:~3,2%%time:~6,2% echo %backupfiledate% pause

El código anterior simplemente crea una fecha para anexar al final de su archivo. A continuación, la primera declaración de bcp con la vista hacia el cte recursivo para concatenarlo todo junto.

bcp "SELECT * FROM [dbo].[vwxMYSAMPLE_EXTRACT_COLUMNS] OPTION (MAXRECURSION 300)" queryout C:/Temp/Col_NM%backupfiledate%.txt -c -t"|" -S MYSERVERTOLOGINTO -T -q bcp "SELECT * FROM [myDBName].[dbo].[vwxMYSAMPLE_EXTRACT_NEW] " queryout C:/Temp/3316_PHYSDATA_ALL%backupfiledate%.txt -c -t"|" -S MYSERVERTOLOGINTO -T -q

Ahora combínalos usando el comando copiar

copy C:/Temp/Col_NM%backupfiledate%.txt + C:/Temp/3316_PHYSDATA_ALL%backupfiledate%.txt C:/Temp/3316_PHYSDATA_ALL%backupfiledate%.csv

Todo listo

Encuentre a continuación otra forma de hacer lo mismo. Este procedimiento también toma en parámetro un nombre de esquema en caso de que lo necesite para acceder a su tabla.

CREATE PROCEDURE Export_Data_NBA @TableName nchar(50), @TableSchema nvarchar(50) = '''' AS DECLARE @TableToBeExported as nvarchar(50); DECLARE @OUTPUT TABLE (col1 nvarchar(max)); DECLARE @colnamestable VARCHAR(max); select @colnamestable = COALESCE(@colnamestable, '''') +COLUMN_NAME+ '','' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName order BY ORDINAL_POSITION SELECT @colnamestable = LEFT(@colnamestable,DATALENGTH(@colnamestable)-1) INSERT INTO @OUTPUT select @colnamestable DECLARE @selectstatement VARCHAR(max); select @selectstatement = COALESCE(@selectstatement, '''') + ''Convert(nvarchar(100),''+COLUMN_NAME+'')+'''',''''+'' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName order BY ORDINAL_POSITION SELECT @selectstatement = LEFT(@selectstatement,DATALENGTH(@selectstatement)-1) DECLARE @sqlstatment as nvarchar(max); SET @TableToBeExported = @TableSchema+''.''+@TableToBeExported SELECT @sqlstatment = N''Select ''+@selectstatement+N'' from ''+@TableToBeExported INSERT INTO @OUTPUT exec sp_executesql @stmt = @sqlstatment SELECT * from @OUTPUT

Este método genera automáticamente nombres de columna con sus datos de fila usando BCP .

El script escribe un archivo para los encabezados de columna (leídos de la tabla INFORMATION_SCHEMA.COLUMNS ) y luego agrega otro archivo con los datos de la tabla.

El resultado final se combina en TableData.csv que tiene los encabezados y los datos de fila. Simplemente reemplace las variables de entorno en la parte superior para especificar el servidor, la base de datos y el nombre de la tabla.

set BCP_EXPORT_SERVER=put_my_server_name_here set BCP_EXPORT_DB=put_my_db_name_here set BCP_EXPORT_TABLE=put_my_table_name_here BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + '','', '''') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=''%BCP_EXPORT_TABLE%''; select @colnames;" queryout HeadersOnly.csv -c -T -S%BCP_EXPORT_SERVER% BCP %BCP_EXPORT_DB%.dbo.%BCP_EXPORT_TABLE% out TableDataWithoutHeaders.csv -c -t, -T -S%BCP_EXPORT_SERVER% set BCP_EXPORT_SERVER= set BCP_EXPORT_DB= set BCP_EXPORT_TABLE= copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv del HeadersOnly.csv del TableDataWithoutHeaders.csv

Tenga en cuenta que si necesita proporcionar credenciales, reemplace la opción -T por -U mi_nombre de usuario -P mi_contraseña

Este método tiene la ventaja de tener siempre los nombres de columna sincronizados con la tabla utilizando INFORMATION_SCHEMA.COLUMNS . La desventaja es que crea archivos temporales. Microsoft realmente debería arreglar la utilidad bcp para soportar esto.

Esta solución utiliza el truco de concatenación de filas SQL desde here combinado con las ideas de bcp de here

Estuve tratando de imaginar cómo hacerlo recientemente y aunque me gusta la solución más popular en la parte superior, simplemente no me funcionaba porque necesitaba los nombres para ser los alias que ingresé en el script, así que usé algunos archivos por lotes (con la ayuda de un colega) para lograr nombres de tabla personalizados.

El archivo de proceso por lotes que inicia el bcp tiene una línea en la parte inferior del script que ejecuta otro script que combina un archivo de plantilla con los nombres de los encabezados y el archivo que acaba de exportarse con bcp usando el siguiente código. Espero que esto ayude a alguien más que estaba en mi situación.

echo Add headers from template file to exported sql files.... Echo School 0031 copy e:/genin/templates/TEMPLATE_Courses.csv + e:/genin/0031/courses0031.csv e:/genin/finished/courses0031.csv /b

La última versión de sqlcmd agrega la opción -w para eliminar el espacio adicional después del valor del campo; sin embargo, NO pone comillas alrededor de las cadenas, lo que puede ser un problema en CSV cuando se importa un valor de campo que contiene una coma.

Las versiones de todos hacen las cosas un poco diferentes. Esta es la versión que he desarrollado a lo largo de los años. Esta versión parece dar cuenta de todos los problemas que he encontrado. Simplemente complete un conjunto de datos en una tabla y luego pase el nombre de la tabla a este procedimiento almacenado.

Llamo a este procedimiento almacenado así:

EXEC @return_value = *DB_You_Create_The_SP_In*.[dbo].[Export_CSVFile] @DB = N''*YourDB*'', @TABLE_NAME = N''*YourTable*'', @Dir = N''*YourOutputDirectory*'', @File = N''*YourOutputFileName*''

También hay otras dos variables:

@NullBlanks - Esto tomará cualquier campo que no tenga un valor y lo anulará. Esto es útil porque, en el verdadero sentido de la especificación CSV, cada punto de datos debe tener comillas a su alrededor. Si tiene un gran conjunto de datos, esto le ahorrará una buena cantidad de espacio al no tener "" (dos comillas dobles) en esos campos. Si no encuentra esto útil, configúrelo a 0.
@IncludeHeaders: tengo un procedimiento almacenado para generar archivos CSV, por lo que tengo ese indicador en caso de que no quiera encabezados.

Esto creará el procedimiento almacenado:

CREATE PROCEDURE [dbo].[Export_CSVFile] (@DB varchar(128),@TABLE_NAME varchar(128), @Dir varchar(255), @File varchar(250),@NULLBLANKS bit=1,@IncludeHeader bit=1) AS DECLARE @CSVHeader varchar(max)='''' --CSV Header , @CmdExc varchar(max)='''' --EXEC commands , @SQL varchar(max)='''' --SQL Statements , @COLUMN_NAME varchar(128)='''' --Column Names , @DATA_TYPE varchar(15)='''' --Data Types DECLARE @T table (COLUMN_NAME varchar(128),DATA_TYPE varchar(15)) --BEGIN Ensure Dir variable has a backslash as the final character IF NOT RIGHT(@Dir,1) = ''/' BEGIN SET @Dir=@Dir+''/' END --END --BEGIN Drop TEMP Table IF Exists SET @SQL=''IF (EXISTS (SELECT * FROM ''+@DB+''.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''TEMP_''+@TABLE_NAME+'''''')) BEGIN EXEC(''''DROP TABLE [''+@DB+''].[dbo].[TEMP_''+@TABLE_NAME+'']'''') END'' EXEC(@SQL) --END SET @SQL=''SELECT COLUMN_NAME,DATA_TYPE FROM ''+@DB+''.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =''''''+@TABLE_NAME+'''''' ORDER BY ORDINAL_POSITION'' INSERT INTO @T EXEC (@SQL) SET @SQL='''' WHILE exists(SELECT * FROM @T) BEGIN SELECT top(1) @DATA_TYPE=DATA_TYPE,@COLUMN_NAME=COLUMN_NAME FROM @T IF @DATA_TYPE LIKE ''%char%'' OR @DATA_TYPE LIKE ''%text'' BEGIN IF @NULLBLANKS = 1 BEGIN SET @SQL+=''CASE PATINDEX(''''%[0-9,a-z]%'''',''+@COLUMN_NAME+'') WHEN ''''0'''' THEN NULL ELSE ''''"''''+RTRIM(LTRIM(''+@COLUMN_NAME+''))+''''"'''' END AS [''+@COLUMN_NAME+''],'' END ELSE BEGIN SET @SQL+=''''''"''''+RTRIM(LTRIM(''+@COLUMN_NAME+''))+''''"'''' AS [''+@COLUMN_NAME+''],'' END END ELSE BEGIN SET @SQL+=@COLUMN_NAME+'','' END SET @CSVHeader+=''"''+@COLUMN_NAME+''",'' DELETE top(1) @T END IF LEN(@CSVHeader)>1 BEGIN SET @CSVHeader=RTRIM(LTRIM(LEFT(@CSVHeader,LEN(@CSVHeader)-1))) END IF LEN(@SQL)>1 BEGIN SET @SQL= ''SELECT ''+ LEFT(@SQL,LEN(@SQL)-1) + '' INTO [''+@DB+''].[dbo].[TEMP_''+@TABLE_NAME+''] FROM [''+@DB+''].[dbo].[''+@TABLE_NAME+'']'' END EXEC(@SQL) IF @IncludeHeader=0 BEGIN --BEGIN Create Data file SET @CmdExc =''BCP "''+@DB+''.dbo.TEMP_''+@TABLE_NAME+''" out "''+@Dir+''Data_''+@TABLE_NAME+''.csv" /c /t, -T'' EXEC master..xp_cmdshell @CmdExc --END SET @CmdExc =''del ''+@Dir+@File EXEC master..xp_cmdshell @CmdExc SET @CmdExc =''ren ''+@Dir+''Data_''+@TABLE_NAME+''.csv ''+@File EXEC master..xp_cmdshell @CmdExc END else BEGIN --BEGIN Create Header and main file SET @CmdExc =''echo ''+@CSVHeader+''> ''+@Dir+@File EXEC master..xp_cmdshell @CmdExc --END --BEGIN Create Data file SET @CmdExc =''BCP "''+@DB+''.dbo.TEMP_''+@TABLE_NAME+''" out "''+@Dir+''Data_''+@TABLE_NAME+''.csv" /c /t, -T'' EXEC master..xp_cmdshell @CmdExc --END --BEGIN Merge Data File With Header File SET @CmdExc = ''TYPE ''+@Dir+''Data_''+@TABLE_NAME+''.csv >> ''+@Dir+@File EXEC master..xp_cmdshell @CmdExc --END --BEGIN Delete Data File SET @CmdExc = ''DEL /q ''+@Dir+''Data_''+@TABLE_NAME+''.csv'' EXEC master..xp_cmdshell @CmdExc --END END --BEGIN Drop TEMP Table IF Exists SET @SQL=''IF (EXISTS (SELECT * FROM ''+@DB+''.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''TEMP_''+@TABLE_NAME+'''''')) BEGIN EXEC(''''DROP TABLE [''+@DB+''].[dbo].[TEMP_''+@TABLE_NAME+'']'''') END'' EXEC(@SQL)

Lo logré con éxito con el siguiente código. Ponga debajo el código en la nueva ventana de consulta de SQL Server y pruebe

CREATE TABLE tempDBTableDetails ( TableName VARCHAR(500), [RowCount] VARCHAR(500), TotalSpaceKB VARCHAR(500), UsedSpaceKB VARCHAR(500), UnusedSpaceKB VARCHAR(500) ) -- STEP 1 :: DECLARE @cmd VARCHAR(4000) INSERT INTO tempDBTableDetails SELECT ''TableName'', ''RowCount'', ''TotalSpaceKB'', ''UsedSpaceKB'', ''UnusedSpaceKB'' INSERT INTO tempDBTableDetails SELECT S.name +''.''+ T.name as TableName, Convert(varchar,Cast(SUM(P.rows) as Money),1) as [RowCount], Convert(varchar,Cast(SUM(a.total_pages) * 8 as Money),1) AS TotalSpaceKB, Convert(varchar,Cast(SUM(a.used_pages) * 8 as Money),1) AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables T INNER JOIN sys.partitions P ON P.OBJECT_ID = T.OBJECT_ID INNER JOIN sys.schemas S ON T.schema_id = S.schema_id INNER JOIN sys.allocation_units A ON p.partition_id = a.container_id WHERE T.is_ms_shipped = 0 AND P.index_id IN (1,0) GROUP BY S.name, T.name ORDER BY SUM(P.rows) DESC -- SELECT * FROM [FIINFRA-DB-SIT].dbo.tempDBTableDetails ORDER BY LEN([RowCount]) DESC SET @cmd = ''bcp "SELECT * FROM [FIINFRA-DB-SIT].dbo.tempDBTableDetails ORDER BY LEN([RowCount]) DESC" queryout "D:/Milind/export.xls" -U sa -P dbowner -c'' Exec xp_cmdshell @cmd --DECLARE @HeaderCmd VARCHAR(4000) --SET @HeaderCmd = ''SELECT ''''TableName'''', ''''RowCount'''', ''''TotalSpaceKB'''', ''''UsedSpaceKB'''', ''''UnusedSpaceKB'''''' exec master..xp_cmdshell ''BCP "SELECT ''''TableName'''', ''''RowCount'''', ''''TotalSpaceKB'''', ''''UsedSpaceKB'''', ''''UnusedSpaceKB''''" queryout "d:/milind/header.xls" -U sa -P dbowner -c'' exec master..xp_cmdshell ''copy /b "d:/Milind/header.xls"+"d:/Milind/export.xls" "d:/Milind/result.xls"'' exec master..xp_cmdshell ''del "d:/Milind/header.xls"'' exec master..xp_cmdshell ''del "d:/Milind/export.xls"'' DROP TABLE tempDBTableDetails

Lo más fácil es usar la opción queryout y usar union all para vincular una lista de columnas con el contenido real de la tabla

bcp "select ''col1'', ''col2'',... union all select * from myschema.dbo.myTableout" queryout myTable.csv /SmyServer01 /c /t, -T

Un ejemplo:

create table Question1355876 (id int, name varchar(10), someinfo numeric) insert into Question1355876 values (1, ''a'', 123.12) , (2, ''b'', 456.78) , (3, ''c'', 901.12) , (4, ''d'', 353.76)

Esta consulta devolverá la información con los encabezados como primera fila. (Tenga en cuenta los moldes de los valores numéricos)

select ''col1'', ''col2'', ''col3'' union all select cast(id as varchar(10)), name, cast(someinfo as varchar(28)) from Question1355876

El comando bcp será:

bcp "select ''col1'', ''col2'', ''col3'' union all select cast(id as varchar(10)), name, cast(someinfo as varchar(28)) from Question1355876" queryout myTable.csv /SmyServer01 /c /t, -T

Por lo que sé, BCP solo exporta los datos; no creo que haya ninguna forma de exportar la fila de encabezado con los nombres de las columnas también.

Una técnica común que se ve para resolver esto es usar una vista sobre sus datos reales para exportar, que básicamente hace un UNION TODO sobre dos declaraciones:

la primera declaración para devolver una fila con los encabezados de columna
los datos reales que se exportarán

y luego use bcp en esa vista, en lugar de su tabla de datos subyacente directamente.

Bagazo

Una buena alternativa es SqlCmd, ya que incluye encabezados, pero tiene la desventaja de agregar relleno de espacio alrededor de los datos para la legibilidad humana. Puede combinar SqlCmd con la utilidad GnuWin32 sed (edición de flujo) para limpiar los resultados. Aquí hay un ejemplo que funcionó para mí, aunque no puedo garantizar que sea a prueba de balas.

Primero, exporta los datos:

sqlcmd -S Server -i C:/Temp/Query.sql -o C:/Temp/Results.txt -s" "

El -s" " es un carácter de tabulación entre comillas dobles. Descubrí que debe ejecutar este comando a través de un archivo por lotes; de lo contrario, el símbolo del sistema de Windows tratará la pestaña como un comando de finalización automática y sustituirá un nombre de archivo en lugar de la pestaña.

Si Query.sql contiene:

SELECT name, object_id, type_desc, create_date FROM MSDB.sys.views WHERE name LIKE ''sysmail%''

entonces verá algo como esto en Results.txt

name object_id type_desc create_date ------------------------------------------- ----------- ------------------- ----------------------- sysmail_allitems 2001442204 VIEW 2012-07-20 17:38:27.820 sysmail_sentitems 2017442261 VIEW 2012-07-20 17:38:27.837 sysmail_unsentitems 2033442318 VIEW 2012-07-20 17:38:27.850 sysmail_faileditems 2049442375 VIEW 2012-07-20 17:38:27.860 sysmail_mailattachments 2097442546 VIEW 2012-07-20 17:38:27.933 sysmail_event_log 2129442660 VIEW 2012-07-20 17:38:28.040 (6 rows affected)

A continuación, analiza el texto usando sed:

Tenga en cuenta que el comando 2d significa eliminar la segunda línea, el comando $d significa eliminar la última línea y "/^$/d" borra cualquier línea en blanco.

El archivo limpio se ve así (aunque reemplacé las pestañas con | para que puedan visualizarse aquí):

name|object_id|type_desc|create_date sysmail_allitems|2001442204|VIEW|2012-07-20 17:38:27.820 sysmail_sentitems|2017442261|VIEW|2012-07-20 17:38:27.837 sysmail_unsentitems|2033442318|VIEW|2012-07-20 17:38:27.850 sysmail_faileditems|2049442375|VIEW|2012-07-20 17:38:27.860 sysmail_mailattachments|2097442546|VIEW|2012-07-20 17:38:27.933 sysmail_event_log|2129442660|VIEW|2012-07-20 17:38:28.040

Yo estaba teniendo el mismo problema. Necesitaba exportar el encabezado de columna usando la utilidad SQL server bcp. De esta forma, exporté los "encabezados" de tabla con los datos en el mismo archivo exportado de una vez.

DECLARE @table_name VARCHAR(50) =''mytable'' DECLARE @columnHeader VARCHAR(8000) SELECT @columnHeader = COALESCE(@columnHeader+'','' ,'''')+ ''''''''+column_name +'''''''' FROM Nal2013.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table_name SELECT @raw_sql = ''bcp "SELECT ''+ @columnHeader +'' UNION ALL SELECT * FROM mytable" queryout c:/datafile.csv -c -t, -T -S ''+ @@servername EXEC xp_cmdshell @raw_sql

Feliz codificación :)