I have the following code to do a bulk insert into sql server (taken from the example at the bottom of here):
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 fail and 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 because there is an extra line on the end of the txt file being created so it is trying to insert a blank row when it gets to the end of the file (and all the other rows have an identity set for them.)
Is there any way to get this to work properly, as I have multiple massive dbs that I am trying to use this on?
As it turns out I needed the correct arguments adding to the query. In the end, this is the query that has 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With