具有列名称的SQL BCP [英] SQL BCP with column name

查看:71
本文介绍了具有列名称的SQL BCP的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试使用BCP将存储过程导出到.csv文件.它的确为我提供了.CSV格式的输出文件,但没有打印列名.下面是脚本.请看一下,让我知道我想念的东西

I am trying to export my stored procedure to a .csv file using BCP. It does give me a output file in .CSV but it does not print column name. Below is the script. Please look at and let me know what i am missing

    DECLARE @command VARCHAR(4000)
    declare @fulldate varchar(30) = convert(varchar,GETDATE(),112)
    declare @year varchar(30) = left(@fulldate,4)
    declare @day  varchar(30) = right(@fulldate,2)
    declare @month varchar(30) = left(right(@fulldate,4),2)
    DECLARE @FileDirectory VARCHAR(1000) = 'c:\'
    DECLARE @FileName VARCHAR(255)= 'TestingDOC' + @month + '.' + @day + '.'      + @year  + '.txt'
    declare @attach varchar(1255) = @fileDirectory + @fileName

    SET @command = 'bcp "select * from ngprod.dbo.TEMP_PAS"'
        + ' queryout "' + @FileDirectory + @FileName + '"'
        + ' -c -t, -T -S'+ @@servername
    EXEC master..xp_cmdshell @command

推荐答案

经过大量的尝试和错误,下面是有关如何添加列的答案

After a lot of trail and error below is the answer on how to add column

首先创建一个header.txt文件(在头文件中添加您所有人的头文件)例如,如果头文件需要名字,姓氏等

First create a header.txt file (inside the header file add all of you header) for example if the header file need firstname, lastname etc

第二次将以下查询粘贴到您的存储过程中

second paste the below query in your stored procedure

DECLARE @command VARCHAR(4000)

DECLARE @FileDirectory VARCHAR(1000) = 'c:\test\'

DECLARE @HeaderFile varchar(255) = 'Headers.txt'

DECLARE @FileName VARCHAR(255)

SET @FileName = 'TestFile_' + CONVERT(VARCHAR,GETDATE(),112)

SET @command = 'bcp "select * from TESTDB.dbo.TEST_Table"'

       + ' queryout "' + @FileDirectory + @FileName + '.txt"'

       + ' -c -q -t, -T  -S'+@@servername

EXEC master..xp_cmdshell @command

SET @command =  'copy "' + @FileDirectory + @HeaderFile + '"+"' + @FileDirectory + @FileName + '.txt"' +  ' "' + @FileDirectory + @filename + '.csv"'

EXEC master..xp_cmdshell @command

SET @command = 'del "' + @FileDirectory + @FileName + '.txt"'

EXEC master..xp_cmdshell @command

这篇关于具有列名称的SQL BCP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆