减少bcp导出的输出

Question

在我们的项目中，我们使用bcp命令导出大约百万行，并将输出记录到输出文件中。对于bcp导入，我可以使用-b开关来控制bcp命令的输出，该开关指定了no。要批量导入的行数。输出如下所示：

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
1000 rows sent to SQL Server. Total sent: 11000
1000 rows sent to SQL Server. Total sent: 12000
SQLState = 22001, NativeError = 0

可以通过增加使用-b交换机发送的数量来轻松减少：

Starting copy...
10000 rows sent to SQL Server. Total sent: 10000
SQLState = 22001, NativeError = 0

12406 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 75     Average : (165413.3 rows per sec.)

但是对于bcp导出，我无法控制输出，并且对于一百万行，日志变得太大，例如。下面的命令

bcp  Temp.dbo.TestTable out outdata.txt -t , -f file.fmt -S Server -U user-P password -m 10

输出如下：

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL.
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
1000 rows successfully bulk-copied to host-file. Total received: 6000
1000 rows successfully bulk-copied to host-file. Total received: 7000
1000 rows successfully bulk-copied to host-file. Total received: 8000
1000 rows successfully bulk-copied to host-file. Total received: 9000
1000 rows successfully bulk-copied to host-file. Total received: 10000
1000 rows successfully bulk-copied to host-file. Total received: 11000
1000 rows successfully bulk-copied to host-file. Total received: 12000
1000 rows successfully bulk-copied to host-file. Total received: 13000
1000 rows successfully bulk-copied to host-file. Total received: 14000
1000 rows successfully bulk-copied to host-file. Total received: 15000
1000 rows successfully bulk-copied to host-file. Total received: 16000
1000 rows successfully bulk-copied to host-file. Total received: 17000
1000 rows successfully bulk-copied to host-file. Total received: 18000
1000 rows successfully bulk-copied to host-file. Total received: 19000
1000 rows successfully bulk-copied to host-file. Total received: 20000
1000 rows successfully bulk-copied to host-file. Total received: 21000
1000 rows successfully bulk-copied to host-file. Total received: 22000

我尝试过用bcp out传递-b开关，但它总是以1000个为一批导出它们，并且按greping或seding筛选行将花费太多时间。谢谢你的帮助。

Answer 1

在bcp中似乎没有解决这个问题的办法。但是，有一种解决方法；将bcp命令行打包到xp_cmdshell语句中，并指定no_output选项：

EXEC xp_cmdshell "bcp  Temp.dbo.TestTable out outdata.txt -t , -f file.fmt -S Server -U user-P password -m 10", no_output

来源：click here

Answer 2

1. 您可以使用>将输出重定向到文件

bcp sometable out outfile -S Server -U user -P password > export.log

注意末尾的> export.log位。这将用日志填充export.log。因此，如果您的命令失败，您可以检查。有关此方法的更多详细信息，请参见here。

• bcp还提供了输出参数-o

bcp sometable out outfile -S Server -U user -P password -o export.log

这一次请注意最后的-o export.log。

Answer 3

Block命令行输出：

bcp Temp.dbo.TestTable out outdata.txt -t , -f file.fmt -S Server -U user-P password -m 10>nul