具有列名称的SQL BCP [英] SQL BCP with column name
本文介绍了具有列名称的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屋!
查看全文