使用两个行终止符的BULK INSERT

Question

我正在尝试导入一个文本文件，结果应该是一个列中每行都是单独的文字。 例如以下文本：

'你好妈妈,

我们又见面了'

应该生成5个记录:

'Hello' 
'Mom,'
'we' 
'meet' 
'again'

我尝试使用BULK INSERT和ROWTERMINATOR = ' '来实现这一目标，但问题在于把new line也视为终止符，导致结果中有一个'Mom,we'。

据我所知，BULK INSERT没有添加second ROWTEMRMINATOR的方法（对吗？）。 你知道实现上述结果的最佳方法吗？

该文件无法在SQL Server之外进行预处理，而且该方法应适用于数百个包含成千上万行单词的文件，不仅仅是一次性导入。

- user3853657

---

我认为在这个单词解析任务中使用 BULK INSERT 不是最佳选择。 - peter.petrov

有很多方法可以“重新格式化”文件，以便批量插入到源计算机上。使用其中一个工具来处理数据。试图使用BULK INSERT是错误的。 - Hogan

你能预处理文件吗？只需要几行C#代码就可以完成这个任务。 - usr

谢谢您的评论。我已经添加了一些细节来澄清。我知道BULK INSERT并不是最好的选择，问题是什么是最好的选择？;) - user3853657

亲爱的用户，我之前已经添加了问题的详细信息 ;) “文件无法在 SQL Server 之外进行预处理”。 - user3853657

我很好奇为什么不能使用外部解析器？ - Martin Brown

Answer 1

给定:

该文件不能在SQL Server之外进行预处理

选项1

为什么不使用OPENROWSET(BULK...)？这将允许您导入/插入（同时处理行终止符）并同时拆分（处理字段终止符）。根据是否可以创建格式化文件，它应该类似于以下内容之一：

格式化文件=拆分每一行

INSERT INTO dbo.TableName (ColumnName)
  SELECT split.SplitVal
  FROM   OPENROWSET(BULK 'path\to\file.txt',
                    FORMATFILE='Path\to\FormatFile.XML') data(eachrows)
  CROSS APPLY SQL#.String_Split(data.eachrow, N' ', 2) split;

无格式文件 = 将整个文件拆分为单个行

INSERT INTO dbo.TableName (ColumnName)
  SELECT split.SplitVal
  FROM   OPENROWSET(BULK 'path\to\file.txt', SINGLE_CLOB) data(allrows)
  CROSS APPLY SQL#.String_Split(
                                REPLACE(data.allrows, NCHAR(10), N' '),
                                N' ',
                                2 -- remove empty entries
                               ) split;

备注：

• For both methods you need to use a string splitter. SQLCLR-based splitters are the fastest and in the examples above I used one from the SQL# library (which I created but the String_Split function is available in the Free version). You can also write your own. If you do write your own and are not using a Format File, it might be a good idea to allow for multiple split characters so you can pass in both " " and "\n" and get rid of the REPLACE().

• If you can write your own SQLCLR string splitter, then it might be even better to just write a SQLCLR stored procedure that accepts an input parameter for @FilePath, reads the file, does the splitting, and spits out the words as many rows of a single column:

  INSERT INTO dbo.TableName(ColumnName)
    EXEC dbo.MySQLCLRproc(N'C:\path\to\file.txt');
  

• If you are not using (or cannot use) a Format File, then be sure to use the proper "SINGLE_" option as you can do either SINGLE_CLOB (returns VARCHAR(MAX) for standard ASCII file) or SINGLE_NCLOB (returns NVARCHAR(MAX) for Unicode file).

• Even if you can create a Format File, it might be more efficient to pull in the entire file as a single string, depending on the size of the files, as splitting a large string can be done rather quickly and would be a single function call, whereas a file of thousands of short lines would be thousands of function calls that are also fast, but likely not 1000 times faster than the single call. But if the file is 1 MB or larger then I would probably still opt for doing the Format File and processing as many short lines.

选项2

如果您所说的“预处理”是指更改，但没有限制仅读取它们并插入来自SQL Server外部的数据，则应编写一个小的.NET应用程序，该应用程序读取行，拆分行，并通过调用接受表值参数（TVP）的存储过程将数据插入。我在S.O.上的另一个答案中详细介绍了这种方法：

如何在最短时间内插入1000万条记录？

这可以编译为控制台应用程序，并用于批处理（即.CMD / .BAT）脚本，甚至可以计划为Windows任务。

- Solomon Rutzky