The customer reported that they could not BCP into a table using “keepidentity” and after more debugging I found that “keepidentity” was not the issue but the issue was the addition of a clustered index.
SQLState = 37000, NativeError = 511 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server] Cannot create a row of size 8161 which is greater than the allowable maximum row size of 8060.
I was able to track the behavior the use of a sort for the bulk operation. When an index/constraint is present bulk insert may choose to sort the incoming data in a temporary table. The incoming stream is using a sort and the additional overhead for the sort causes the row to exceed the size limit.
BCP SORTS: Bulk Insert/BCP uses a set of heuristics to determine when to inject and sort the incoming data. In general, bcp.exe and bulk insert are considered unknown sort sources and a sort will be injected to handle index/constraint operations.
You can use the following to get a ballpark idea of the row size. In this customer’s example the size was just under the row limit. When the sort is injected the row size exceeds the limit, generating to the error.
fromsyscolumnswhere id =object_id('MyTable')and xusertype = 167
set @dynamicsql = @dynamicsql +') as rowsize from MyTable2 order by DataId'
Workaround There are various workarounds to the issue. Use non-clustered index, drop the index, import, and create the index, or BCP into a table without an index and then insert into the target table.
Stack Proof The following stack shows the Bulk operation with the additional sort taking place.