SQL Server批量插入Ingore或跳过最后一行 [英] SQL Server Bulk Insert Ingore or skip last row

问题描述

我正在使用批量插入脚本将大量平面文件导入SQL Server

I'm using a bulk insert script to import a number of flat files into SQL Server

一些文件以

-----------------------------------

所以我想做的就是跳过最后一行，或者删除批量插入中的 ------------------ .这些选项之一可能吗?

So what I want to do is or skip last row(s) or remove ------------------ in the bulk insert. Is one of these options possible?

SET @s = N'BULK INSERT ' + @t + '
           FROM ''' + @f + '''
           WITH (FIELDTERMINATOR = ''|'',
                 ROWTERMINATOR = ''0x0a'',
                 FIRSTROW=2)  '

lastrow = 1 不起作用

推荐答案

我能想到的唯一方法是首先将整个文件批量插入到单个列表中(如 varchar(max)).比您可以确定最后一行，然后在实际的批量插入中使用该值.

The only way I can think of is to first bulk insert the whole file into a single column table (as varchar(max)). Than you can identify the last row, and use that value in your actual bulk insert.

这不是一个非常简单的方法，但是我认为没有另一种方法(除非您使用C#或Java或任何其他语言编写自定义解决方案).也许您可以使用 SQLCMD 首先读取文件中的行数，但是我不知道如何.

This is not a very straight forward approach, but I don't think there is another (unless you write a custom solution in C# or java or whatever). Maybe you can use SQLCMD to first read the number of lines in the file, but I don't know how.

请注意，有一个连接项目.微软在该页面上建议使用 openrowset 解决方案，可能值得尝试，但是我怀疑它是否可以在您的情况下使用.

Please note there is a connect item which Microsoft has closed. On that page Microsoft suggests using an openrowset solution, could be worthwhile to try, but I doubt it would work in your situation.

这篇关于SQL Server批量插入Ingore或跳过最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！