Bulk insert error: explicit value must be specified

Question

I have the following code to do bulk insertion into sql server ( taken from the example below at the bottom ):

SET IDENTITY_INSERT [dbo].Reporting_DriverScoreCard ON EXECUTE XP_CMDSHELL 'BCP AT100Reporting.dbo.Reported_Driver_ScoreCard out D:\temp\Reported_Driver_ScoreCard.txt -T -n' BULK INSERT [dbo].Reporting_DriverScoreCard FROM 'D:\temp\Reported_Driver_ScoreCard.txt' WITH ( DATAFILETYPE = 'native', ERRORFILE = 'D:\temp\error.txt', MAXERRORS = 10000 ); SET IDENTITY_INSERT [dbo].Reporting_DriverScoreCard OFF 

However, when I run this command, it will not work and will give me this error message:

Explicit value must be specified for identity column in table 'Reporting_DriverScoreCard' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

I think this is due to the fact that there is an additional line at the end of the created txt file, so it tries to insert an empty line when it reaches the end of the file (and all other lines have a set of identifiers for them.)

Is there a way to get this to work correctly since I have some massive dbs that I'm trying to use?

Answer 1

As it turns out, I need the right arguments to add to the request. In the end, this is a query that worked for me:

 EXECUTE XP_CMDSHELL 'BCP AT100Reporting.dbo.Reported_Driver_ScoreCard out D:\temp\Reported_Driver_ScoreCard.dat -T -E -n -k' SET IDENTITY_INSERT [dbo].Reporting_DriverScoreCard ON BULK INSERT [dbo].Reporting_DriverScoreCard FROM 'D:\temp\Reported_Driver_ScoreCard.dat' WITH ( KEEPIDENTITY, BATCHSIZE = 5000, DATAFILETYPE = 'native', ERRORFILE = 'D:\temp\error.txt', MAXERRORS = 10000, KEEPNULLS ); SET IDENTITY_INSERT [dbo].Reporting_DriverScoreCard OFF