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屋!