使用bcp实用工具和SQL Server 2008将表导出到具有列标题(列名)的文件

最简单的是使用 queryout 选择与使用 union all 将列列表与实际表内容链接的步骤

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

一个例子:

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)

此查询将返回标题为第一行的信息。(注意数值的转换)

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

bcp命令将是:

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

此方法使用以下命令自动输出列名称和行数据: BCP .

脚本为列标题写入一个文件(从 INFORMATION_SCHEMA.COLUMNS 表),然后用表数据附加另一个文件。

最终输出合并为 TableData.csv 其中包含标题和行数据。只需替换顶部的环境变量即可指定服务器、数据库和表名。

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

请注意,如果需要提供凭据,请将-t选项替换为-u my_用户名-p my_密码

此方法的优点是始终使用 信息栏 . 缺点是它会创建临时文件。微软真的应该修复BCP实用程序来支持这一点。

此解决方案使用来自 here 结合BCP的想法 here

一个很好的替代方法是sqlcmd,因为它确实包含头,但它的缺点是在数据周围添加空格填充,以提高可读性。您可以将sqlcmd与gnuwin32 sed(流编辑)实用程序结合使用以清除结果。这是一个对我有用的例子,尽管我不能保证它是防弹的。

首先,导出数据:

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

这个 -s" " 是双引号中的制表符。我发现您必须通过批处理文件运行此命令,否则Windows命令提示符将把选项卡视为自动完成命令,并用文件名代替选项卡。

如果query.sql包含:

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

然后您将在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)

接下来,使用sed解析文本:

sed -r "s/ +\t/\t/g" C:\Temp\Results.txt | sed -r "s/\t +/\t/g" | sed -r "s/(^ +| +$)//g" | sed 2d | sed $d | sed "/^$/d" > C:\Temp\Results_New.txt

请注意 2d 命令意味着删除第二行, $d 命令意味着删除最后一行,以及 "/^$/d" 删除所有空行。

清理后的文件看起来像这样(尽管我用 | 因此可以在这里看到它们):

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

我最近一直在想如何做到这一点,虽然我喜欢顶部最流行的解决方案,但它对我来说根本不起作用,因为我需要在脚本中输入别名的名称,所以我使用一些批处理文件(在同事的帮助下)来完成自定义表名。

启动bcp的批处理文件在脚本底部有一行,该行执行另一个脚本,该脚本将模板文件与头文件名合并,并使用下面的代码将刚与bcp导出的文件与bcp合并。希望这能帮助我遇到的其他人。

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

我也有同样的问题。我需要使用SQL Server BCP实用工具导出列标题。这样我就可以一次性将带有数据的表“headers”导出到相同的导出文件中。

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

快乐编码:)

据我所知,bcp只导出数据——我认为没有任何方法可以让它导出带有列名的标题行。

解决这一问题的一种常见技术是使用实际数据的视图进行导出,这基本上是对两个语句进行联合:

• 返回一行和列标题的第一条语句
• 要导出的实际数据

然后在该视图上使用bcp,而不是直接使用基础数据表。

马克

以及Marc_的解决方案,您也可以使用 osql 或 sqlcmd

这包括头文件,它可以像bcp一样使用-q和-o。但是,它们不支持bcp这样的格式文件。

这里有一个非常简单的存储过程,它也可以实现这个技巧。

    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

每个人的版本都有一些不同。这是我多年来开发的版本。这个版本似乎解释了我遇到的所有问题。只需将数据集填充到表中,然后将表名传递到此存储过程。

我这样调用这个存储过程:

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*'

还有两个其他变量:

• @nullblanks——这将获取任何没有值的字段,并且 它是空的。这很有用,因为从csv的真正意义上讲 规范每个数据点周围都应该有引号。如果你 拥有大量的数据集,这将为您节省大量的空间 在这些字段中没有“”(两个双引号)。如果您不觉得这个有用,那么将它设置为0。
• @includeheaders——我有一个用于输出csv的存储过程 文件,所以在不需要标题的情况下,我有这个标志。

这将创建存储过程:

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)

最新版本的sqlcmd添加了 -w 选项删除字段值后的多余空间;但是,它不会在字符串周围加引号,这在导入包含逗号的字段值时可能会对csv造成问题。

我已经用下面的代码成功地实现了这一点。 在“SQL Server新查询”窗口中放入以下代码并尝试

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 

您应该能够用一个CTE视图和一个包含BCP代码的批处理文件来解决这个问题。首先创建视图。因为它是相对直接的,所以我没有创建临时表,通常是这样

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

现在,创建批处理文件。我在临时目录中创建了这个,但我很懒惰。

  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

上面的代码只是创建一个附加到文件末尾的日期。接下来,第一个bcp语句将视图与递归CTE连接在一起。

  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

现在使用copy命令将它们合并在一起

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

准备就绪

我得到了一个基于我以前看到的版本。它帮助我创建了csv或txt格式的导出文件。我正在将一张桌子存储到一张临时桌子中:

    IF OBJECT_ID('tempdb..##TmpExportFile') IS NOT NULL
DROP TABLE ##TmpExportFile;


DECLARE @columnHeader VARCHAR(8000)
DECLARE @raw_sql nvarchar(3000)

SELECT
              * INTO ##TmpExportFile
              ------ FROM THE TABLE RESULTS YOU WANT TO GET
              ------ COULD BE A TABLE OR A TEMP TABLE BASED ON INNER JOINS
              ------ ,ETC.

FROM TableName -- optional WHERE ....


DECLARE @table_name  VARCHAR(50) = '##TmpExportFile'
SELECT
              @columnHeader = COALESCE(@columnHeader + ',', '') + '''[' + c.name + ']''' + ' as ' + '' + c.name + ''
FROM tempdb.sys.columns c
INNER JOIN tempdb.sys.tables t
              ON c.object_id = t.object_id
WHERE t.NAME = @table_name

print @columnheader

                DECLARE @ColumnList VARCHAR(max)
SELECT
              @ColumnList = COALESCE(@ColumnList + ',', '') + 'CAST([' + c.name + '] AS CHAR(' + LTRIM(STR(max_length)) + '))'
FROM tempdb.sys.columns c
INNER JOIN tempdb.sys.tables t
              ON c.object_id = t.object_id
WHERE t.name = @table_name
print @ColumnList

--- CSV FORMAT                                       
SELECT
              @raw_sql = 'bcp "SELECT ' + @columnHeader + ' UNION all SELECT ' + @ColumnList + ' FROM ' + @table_name + ' " queryout \\networkdrive\networkfolder\datafile.csv -c -t, /S' + ' SQLserverName /T'
--PRINT @raw_sql
EXEC xp_cmdshell @raw_sql

  --- TXT FORMAT
       SET @raw_sql = 'bcp "SELECT ' + @columnHeader + ' UNION all SELECT ' + @ColumnList + ' FROM ' + @table_name + ' " queryout \\networkdrive\networkfolder\MISD\datafile.txt /c /S'+ ' SQLserverName /T'
       EXEC xp_cmdshell @raw_sql



DROP TABLE ##TmpExportFile

请在下面找到另一种方法来制作相同的东西。此过程还接受参数一个模式名,以防您需要它来访问您的表。

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