固定长度的数据文件，在批量插入之前预览结果

Question

>我正在尝试批量插入几个固定长度的数据文件。我已经创建了数据库表和格式化文件。然后我尝试以下操作。例如，我有一个格式化文件，如下所示：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="4"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="1"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="20"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="50"/>
<FIELD ID="5" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="6" xsi:type="CharFixed" LENGTH="4"/>
<FIELD ID="7" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="8" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="9" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="10" xsi:type="CharFixed" LENGTH="1"/>
<FIELD ID="11" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="12" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="13" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="14" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="15" xsi:type="CharFixed" LENGTH="8"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="BSTNUM" xsi:type="SQLCHAR" LENGTH="4"/>
<COLUMN SOURCE="2" NAME="MUTKOD" xsi:type="SQLCHAR" LENGTH="1"/>
<COLUMN SOURCE="3" NAME="MDBST" xsi:type="SQLCHAR" LENGTH="20"/>
<COLUMN SOURCE="4" NAME="MDOBST" xsi:type="SQLCHAR" LENGTH="50"/>
<COLUMN SOURCE="5" NAME="MDBCOD" xsi:type="SQLCHAR" LENGTH="8"/>
<COLUMN SOURCE="6" NAME="MDRECL" xsi:type="SQLCHAR" LENGTH="4"/>
<COLUMN SOURCE="7" NAME="MDDATI" xsi:type="SQLCHAR" LENGTH="8"/>
<COLUMN SOURCE="8" NAME="MDDATW" xsi:type="SQLCHAR" LENGTH="8"/>
<COLUMN SOURCE="9" NAME="MDDATU" xsi:type="SQLCHAR" LENGTH="8"/>
<COLUMN SOURCE="10" NAME="MDSTAT" xsi:type="SQLCHAR" LENGTH="1"/>
<COLUMN SOURCE="11" NAME="MDANM0" xsi:type="SQLCHAR" LENGTH="8"/>
<COLUMN SOURCE="12" NAME="MDANM1" xsi:type="SQLCHAR" LENGTH="8"/>
<COLUMN SOURCE="13" NAME="MDANM2" xsi:type="SQLCHAR" LENGTH="8"/>
<COLUMN SOURCE="14" NAME="MDANM3" xsi:type="SQLCHAR" LENGTH="8"/>
<COLUMN SOURCE="15" NAME="MDANTL" xsi:type="SQLCHAR" LENGTH="8"/>
</ROW>
</BCPFORMAT>

然后在SQL Server 2008命令提示符下，我尝试以下操作：

BULK INSERT
BST012018_testtable
from
'D:testimportBST012018T.txt'
WITH (
FORMATFILE='D:testimportBST012018T_format.xml',
ROWS_PER_BATCH = 1000,
KEEPNULLS
)

并收到错误消息：

将 varchar 数据类型转换为日期时间数据类型会导致值超出范围。

错误可能位于数据文件、固定长度格式化文件或数据库表中的数据类型/列中。换句话说，要使其正常工作，这 3 件事(表、数据文件、格式化文件(必须完全对齐。

我正在处理的数据集有更多这样的文件，其中一些有 50+ 列和>100.000 条记录。因此，找出错误发生的位置需要调整格式化文件，也许需要调整表格，试错等。

所以我的问题是，是否可以以某种方式预览批量插入？从固定长度的数据+格式化文件中SELECT，而不仅仅是直接插入？或者换句话说，如何以SQL Server从这种类型的BULK INSERT命令解释数据的方式预览数据？

Answer 1

我只是想回答我自己的问题。感谢Jeroen Mostert的评论，使用OPENROWSET(BULK..从数据文件中进行选择是我一直在寻找的。

您可以选择 TOP 10 或类似内容，以仅查看前几条记录并检查是否有任何错误。顺便说一句，数据文件和格式化文件必须放在服务器上的文件夹中，SQL Server不能只读取本地PC。

SELECT
TOP 5 *
FROM 
OPENROWSET(
BULK 'D:testimportBST012018T.txt',
FORMATFILE = 'D:testimportBST012018T_format.xml'
--,CODEPAGE = '65001' -- 65001 = UTF-8 supported in SQL Server 2016
) AS a

看到这样的结果数据集的预览，我可以发现末尾缺少一列，并且格式化文件中也没有行终止符(在我的例子中为 Cr Lf(。在 RECORD 部分，我添加了最后一个字段，类型为"CharTerm"，属性为 TERMINATOR，如下所示：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="4"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="1"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="20"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="50"/>
<FIELD ID="5" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="6" xsi:type="CharFixed" LENGTH="4"/>
<FIELD ID="7" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="8" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="9" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="10" xsi:type="CharFixed" LENGTH="1"/>
<FIELD ID="11" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="12" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="13" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="14" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="15" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="16" xsi:type="CharFixed" LENGTH="8"/>
<FIELD ID="17" xsi:type="CharTerm" TERMINATOR="rn"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="BSTNUM" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="2" NAME="MUTKOD" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="3" NAME="MDBST" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="4" NAME="MDOBST" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="5" NAME="MDBCOD" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="6" NAME="MDRECL" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="7" NAME="MDDATI" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="8" NAME="MDDATW" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="9" NAME="MDDATU" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="10" NAME="MDSTAT" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="11" NAME="MDANM0" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="12" NAME="MDANM1" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="13" NAME="MDANM2" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="14" NAME="MDANM3" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="15" NAME="MDANTL" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="16" NAME="EMPTY" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>