BCP不返回错误，但也不复制任何行 [英] BCP returns no errors, but also doesn't copy any rows

问题描述

我正在尝试将.csv文件中的大量数据转储到SQL Server 2012数据库中.我正在研究bcp.而不是进行成千上万的INSERT.

I'm trying to dump a very large set of data from a .csv file into a SQL Server 2012 database. Rather than doing thousands of INSERTs, I'm investigating bcp.

编辑:这是一个自动化的过程，而不是一次性的.我也没有对该数据库的BULK INSERT权限.

This is for an automated process, and not just a one-off. I also do not have BULK INSERT rights to this database.

当我尝试将数据复制到数据库中时，bcp不会返回任何错误，但实际上也不会复制任何内容-它只会返回0 rows copied.我已将其缩减为无法正常工作的最小情况.

When I try to copy data into the database, bcp doesn't return any errors, but also doesn't actually copy anything - it just returns 0 rows copied. I've whittled this down to a minimal case that doesn't work.

首先，创建一个包含两列的简单表:

First, create a simple table with two columns:

CREATE TABLE [dbo].[mincase](
    [key] [varchar](36) NOT NULL,
    [number] [int] NOT NULL

    PRIMARY KEY CLUSTERED 
    (
        [key] ASC
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 40) ON [PRIMARY]
) ON [PRIMARY]

GO

然后，使用bcp从中创建一个格式文件.请注意，此示例创建了一个XML格式的文件，但是对于它是XML还是本机来说都没关系.

Then, use bcp to create a format file from this. Note that this example creates an XML format file, but it doesn't matter whether it's XML or native for this.

bcp MyDB.dbo.mincase format nul -T -n -f mincasexml.fmt -x -S .\SQLEXPRESS

现在创建一个data.csv文件，其中包含一行和两个条目，以制表符分隔.就我而言，该文件就是:

Now create a data.csv file with one row and two entries, tab-delimited. In my case, the file is simply:

somecharacters  12345

同样，这是一个制表符，而不是两个空格，是否有尾随换行符似乎都无关紧要.

Again, that's a tab, not two spaces, and it doesn't seem to matter whether there are trailing newlines or not.

现在尝试将bcp与该格式文件一起使用，以插入该文件中的数据:

Now try to use bcp with that format file to insert the data from this file:

bcp MyDB.dbo.mincase in data.csv -f mincasexml.fmt -T -S .\SQLEXPRESS

我得到的不是将数据复制到数据库中:

Rather than copying data to the database, I get this:

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

有人知道这是怎么回事吗?

Does anyone know what's going on here?

谢谢！

推荐答案

bcp 命令通常需要一个标识符来指定bcp文件的格式模式.

The bcp command typically needs an identifier to specify the format mode of the bcp file.

• -c指定字符(纯文本)模式
• -n指定纯模式
• -w指定unicode模式

在测试用例中，创建的文件是纯文本文件，因此应在命令的 bcp 中指定"-c".

In your test case, the file you created is plaintext, so you should specify '-c' in your bcp in command.

bcp MyDB.dbo.mincase in data.csv -c -T -S .\SQLEXPRESS

Microsoft建议使用"-n"表示导入和导出以避免字段问题分隔符出现在列值中(请参阅字符模式和纯模式最佳实践"部分.)

Microsoft Recommends using the '-n' for imports and exports to avoid issues with field delimiters appearing within column values (See section on Character Mode and Native Mode Best Practices).

这篇关于BCP不返回错误，但也不复制任何行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！