I am using BCP to import rows into SQL server. All seemed to work fine when I realized that BCP is not importing all rows on a random basis. Some data is skipped (sometimes 50%). The BCP procedure is not returning any error. It says successfully copied X rows.
BCP command and output:
bcp MyTable IN MyTable_final.dat -f MyTable.Fmt -S <ServerIP> -U <User> -P <Pwd> -F2
Output:
Starting copy...
6 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 63 Average : (95.24 rows per sec.)
SQLSERVER schema of the table:
CREATE TABLE MyTable (
Type varchar(255) default NULL,
Date varchar(255) default NULL,
ID varchar(255) default NULL,
VID varchar(255) default NULL,
VName varchar(255) default NULL,
LDate varchar(225) default NULL,
id BIGINT IDENTITY(1,1) NOT NULL,
PRIMARY KEY (id) ,
CONSTRAINT d_v UNIQUE (VID, VName)
) ;
BCP Format file being used:
11.0
7
1 SQLCHAR 0 255 "|" 1 Type SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 255 "|" 2 Date SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 255 "|" 3 ID SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 255 "|" 4 VID SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 255 "|" 5 VName SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 225 "|" 6 LDate SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 21 "\r\n" 7 id ""
Actual Data set: You can see there are 13 rows currently in the file. However, in my case, only 6 rows are imported randomly, without any trace of error.
I have not been able to figure out what is going on. Any pointers to trap the missing rows and prevent it?