BULK INSERT 列数不一致 [英] BULK INSERT with inconsistent number of columns

问题描述

我正在尝试使用 BULK INSERT 从平面文件加载 SQL 服务器中的大量数据.但是，我的文件有不同数量的列，例如第一行包含 14 行，第二行包含 4 行.没关系，我只想制作一个包含最大列数的表，并将文件加载到其中，并为缺少列.从那时起我可以玩它.但似乎 SQL Server 在到达行尾并为目标表中的同一行填充更多列时，只是移动到下一行并尝试将该行上的数据放入错误的列表.

I am trying to load a large amount data in SQL server from a flat file using BULK INSERT. However, my file has varying number of columns, for instance the first row contains 14 and the second contains 4. That is OK, I just want to make a table with the max number of columns and load the file into it with NULLs for the missing columns. I can play with it from that point. But it seems that SQL Server, when reaching the end of the line and having more columns to fill for that same row in the destination table, just moves on to the next line and attempts to put the data on that line to the wrong column of the table.

有没有办法获得我正在寻找的行为?有没有我可以用来指定这个的选项?有没有人遇到过这种情况?

Is there a way to get the behavior that I am looking for? Is there an option that I can use to specify this? Has anyone run into this before?

这是代码

BULK INSERT #t
FROM '<path to file>'
WITH 
(
  DATAFILETYPE = 'char',
  KEEPNULLS,
  FIELDTERMINATOR = '#'
)

推荐答案

BULK INSERT 不是特别灵活.一种解决方法是将每一行数据加载到包含单个大 varchar 列的临时表中.加载后，您就可以使用自己的例程解析每一行.

BULK INSERT isn't particularly flexible. One work-around is to load each row of data into an interim table that contains a single big varchar column. Once loaded, you then parse each row using your own routines.

这篇关于BULK INSERT 列数不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！