BCP在输出中添加空间

Question

此BCP声明在我的数据之间添加一个空格。查看图片

这是DDL和示例数据 - 我需要更改哪些内容以便不添加空间？ （空列）

Declare @TestMe Table 
(
field1 varchar(20), 
field2 varchar(20), 
field3 varchar(20), 
field4 varchar(20), 
field5 varchar(20), 
field6 varchar(20), 
field7 varchar(20), 
field8 varchar(20), 
field9 varchar(20), 
field10 varchar(20), 
field11 varchar(20), 
field12 varchar(20), 
field13 varchar(20), 
field14 varchar(20), 
field15 varchar(20), 
field16 varchar(20), 
field17 varchar(20) 
) 

Insert Into @TestMe Values 
('Blue', 'Red', NULL, NULL, NULL, NULL, 'Seven', 'Eight', NULL, NULL, Null, Null, 'Pink', 'Orange', 'NFL', 'Size', 'Last') 

Declare @field1 varchar(20),@sql nvarchar(4000), @filename varchar(100) 
Set @field1 = 'Blue' 
Set @filename = 'C:\Test.csv' 
Set @sql = 'bcp "SELECT ''one'',''two'',''three'',''four'',''five'',''six'',''seven'',''eight'',''nine'',''ten'',''eleven'',''twelve'',''thirteen'',''fourteen'',''fifteen'',''sixteen'',''seventeen'' UNION ALL SELECT CHAR(34)+CONVERT(varchar(10),[field1],101)+CHAR(34),CHAR(34)+CAST([field2] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field3] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field4] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field5] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field6] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field7] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field8] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field9] As VARCHAR(MAX))+CHAR(34),CHAR(34) + CAST([field10] As VARCHAR(MAX))+CHAR(34),CHAR(34) + CAST([field11] As VARCHAR(MAX))+CHAR(34),CHAR(34) + CAST([field12] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field13] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field14] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field15] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([inventorycode] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field16] As VARCHAR(MAX))+CHAR(34) FROM [@TestMe] WHERE field1 = '''[email protected]+'''" queryout "' 
SET @sql = @sql + @filename + '" -c -t, -T -S '[email protected]@SERVERNAME 
exec master..xp_cmdshell @sql 

Answer 1

脚本有2个问题。

首先，您不能在BCP中使用表变量。 BCP是连接到SQL服务器的独立可执行文件，因此它不能访问变量。使用BCP，您必须使用常规表或全局临时表。我已将您的脚本更改为使用全局临时表。

其次，在你的SQL查询中你有[inventorycode]字段，它不在TestMe表中。我删除了它，并添加了field17，这是缺少的。

修复这些问题后，我能够运行不产生间隙纵向输出脚本：

one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen 
"Blue","Red",,,,,"Seven","Eight",,,,,"Pink","Orange","NFL","Size","Last" 

使用下面的代码。

create table ##TestMe 
(
field1 varchar(20), 
field2 varchar(20), 
field3 varchar(20), 
field4 varchar(20), 
field5 varchar(20), 
field6 varchar(20), 
field7 varchar(20), 
field8 varchar(20), 
field9 varchar(20), 
field10 varchar(20), 
field11 varchar(20), 
field12 varchar(20), 
field13 varchar(20), 
field14 varchar(20), 
field15 varchar(20), 
field16 varchar(20), 
field17 varchar(20) 
) 

Insert Into ##TestMe Values 
('Blue', 'Red', NULL, NULL, NULL, NULL, 'Seven', 'Eight', NULL, NULL, Null, Null, 'Pink', 'Orange', 'NFL', 'Size', 'Last') 

Declare @field1 varchar(20),@sql nvarchar(4000), @filename varchar(100) 
Set @field1 = 'Blue' 
Set @filename = 'c\Test.csv' 
Set @sql = 'bcp "SELECT ''one'',''two'',''three'',''four'',''five'',''six'',''seven'',''eight'',''nine'',''ten'',''eleven'',''twelve'',''thirteen'',''fourteen'',''fifteen'',''sixteen'',''seventeen'' UNION ALL SELECT CHAR(34)+CONVERT(varchar(10),[field1],101)+CHAR(34),CHAR(34)+CAST([field2] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field3] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field4] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field5] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field6] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field7] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field8] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field9] As VARCHAR(MAX))+CHAR(34),CHAR(34) + CAST([field10] As VARCHAR(MAX))+CHAR(34),CHAR(34) + CAST([field11] As VARCHAR(MAX))+CHAR(34),CHAR(34) + CAST([field12] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field13] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field14] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field15] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field16] As VARCHAR(MAX))+CHAR(34),CHAR(34)+CAST([field17] As VARCHAR(MAX))+CHAR(34) FROM ##TestMe WHERE field1 = '''[email protected]+'''" queryout "' 
SET @sql = @sql + @filename + '" -c -t, -T -S '[email protected]@SERVERNAME 
exec master..xp_cmdshell @sql