First Observation, limitation with this BCP format file Data File: (Continuous up to three lines)
1;a;b;c;d;e;f;; 2;d;e;f;g;h;i;; 3;g;h;i;j;k;l;;
4;j;k;l;m;n;o;; 5;m;n;o;p;q;r;;
6;p;q;r;s;t;u;;What bcp format file that I'm gonna used to insert 59 columns?
Format File:
9.0
9
1 SQLCHAR 0 0 "" 0 x ""
2 SQLCHAR 0 0 ";" 2 col1 Latin1_General_CI_AS
3 SQLCHAR 0 0 ";" 3 col2 Latin1_General_CI_AS
4 SQLCHAR 0 0 ";" 4 col3 Latin1_General_CI_AS
5 SQLCHAR 0 0 ";" 5 col4 Latin1_General_CI_AS
6 SQLCHAR 0 0 ";" 6 col5 Latin1_General_CI_AS
7 SQLCHAR 0 0 ";" 7 col6 Latin1_General_CI_AS
8 SQLCHAR 0 0 ";" 8 col7 Latin1_General_CI_AS
9 SQLCHAR 0 0 "" 0 x ""
Table structure
Create Table bcp2 (id bigint identity(1,1), col1 varchar(2), col2 varchar(2), col3 varchar(2), col4 varchar(2), col5 varchar(2), col6 varchar(2), col7 varchar(2), dte bit default 0)
When I try to add a another row, it gives this error message,
Msg 4832, Level 16, State 1, Line 1 Bulk load: An unexpected end of file was encountered in the data file. Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
If I arrange the data like this, it gives an error message, Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated.
This arrangement (three continuous lines) gives an error,
1;a;b;c;d;e;f;; 2;d;e;f;g;h;i;; 3;g;h;i;j;k;l;;
4;j;k;l;m;n;o;; 5;m;n;o;p;q;r;;
6;p;q;r;s;t;u;;
This arrangement (one line) does not give error, it will insert all 6 rows,
1;a;b;c;d;e;f;; 2;d;e;f;g;h;i;; 3;g;h;i;j;k;l;; 4;j;k;l;m;n;o;; 5;m;n;o;p;q;r;; 6;p;q;r;s;t;u;;
Second observation, This arrangement of data applies to specific format file
Data file and its arrangement:
1;a;b;c;d;e;f;g;h;i;; 2;d;e;f;g;h;i;j;k;l;; 3;g;h;i;j;k;l;m;n;o;;
4;j;k;l;m;n;o;p;q;r;; 5;m;n;o;p;q;r;s;t;u;;
6;p;q;r;s;t;u;v;w;x;;
... it inserts three rows
1;a;b;c;d;e;f;g;h;i;; 2;d;e;f;g;h;i;j;k;l;; 3;g;h;i;j;k;l;m;n;o;; 4;j;k;l;m;n;o;p;q;r;; 5;m;n;o;p;q;r;s;t;u;; 6;p;q;r;s;t;u;v;w;x;;
... it insert just one row
with this kind of format file:
9.0
12
1 SQLCHAR 0 0 "" 0 x ""
2 SQLCHAR 0 0 ";" 2 col1 Latin1_General_CI_AS
3 SQLCHAR 0 0 ";" 3 col2 Latin1_General_CI_AS
4 SQLCHAR 0 0 ";" 4 col3 Latin1_General_CI_AS
5 SQLCHAR 0 0 ";" 5 col4 Latin1_General_CI_AS
6 SQLCHAR 0 0 ";" 6 col5 Latin1_General_CI_AS
7 SQLCHAR 0 0 ";" 7 col6 Latin1_General_CI_AS
8 SQLCHAR 0 0 ";" 8 col7 Latin1_General_CI_AS
9 SQLCHAR 0 0 ";" 9 col8 Latin1_General_CI_AS
10 SQLCHAR 0 0 ";" 10 col9 Latin1_General_CI_AS
11 SQLCHAR 0 0 ";" 11 col10 Latin1_General_CI_AS
12 SQLCHAR 0 0 "\r\n" 0 x ""
Table structure remains the same,
Create Table bcp (id bigint identity(1,1), col1 varchar(2), col2 varchar(2), col3 varchar(2), col4 varchar(2), col5 varchar(2), col6 varchar(2), col7 varchar(2), col8 varchar(2), col9 varchar(2), col10 varchar(2), dte bit default 0)
Now, my question, what format file that I'm gonna use to get all data from all 59 columns to be inserted properly?
My actual data (part of it):
087728127;V1.10;R;WithMedia;[email protected];0002;Wed Jan 14 09:34:06 2009;NA;Wed Jan 14 09:35:06 2009;0;Origination CANCEL; 487;O;000054;SIP;0067*116;116.93.51.23;01149614244656;208.74.75.250;[email protected];116.93.51.23;14840;208.74.75.250;12348;G.711u_64k,G.711a_64k,RFC 2833;0;0;0;0;0;0;0;050500;SIP;3072151001;208.74.75.250;49614244656;208.80.156.52;[email protected];0.0.0.0;0;208.74.75.250;12350;NA;0;0;0;0;0;0;0;F;01149614244656;0;60;0;0;0000003A 496DB18E 484B6AB4 FA4B4AD0;;
087728128;V1.10;R;WithMedia;[email protected];0002;Wed Jan 14 09:34:49 2009;NA;Wed Jan 14 09:35:12 2009;0;Origination CANCEL; 487;O;050450;SIP;6326378682;116.93.51.98;011420556844118;208.74.75.250;[email protected];116.93.51.98;11096;208.74.75.250;12364;G.729,RFC 2833;437;799;13988;25568;0;0;0;050001;SIP;6326378682;208.74.75.250;011420556844118;204.9.203.210;[email protected];204.9.203.210;11092;208.74.75.250;12366;G.729,RFC 2833;799;437;25568;13988;0;0;0;F;011420556844118;0;7;16;0;0000003E 496DB1B9 44AC3D38 FA4B4AD0;;
... the data is continuous and only separated by spaces after the double semicolon.
This is my actual format file:
9.0
61
1 SQLCHAR 0 0 "" 0 x ""
2 SQLCHAR 0 0 ";" 2 Sequence Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 ";" 3 Version Latin1_General_CP1_CI_AS
4 SQLCHAR 0 0 ";" 4 RecordType Latin1_General_CP1_CI_AS
5 SQLCHAR 0 0 ";" 5 ConnectionType Latin1_General_CP1_CI_AS
6 SQLCHAR 0 0 ";" 6 SessionID Latin1_General_CP1_CI_AS
7 SQLCHAR 0 0 ";" 7 ReleaseCause Latin1_General_CP1_CI_AS
8 SQLCHAR 0 0 ";" 8 StartTime Latin1_General_CP1_CI_AS
9 SQLCHAR 0 0 ";" 9 AnswerTime Latin1_General_CP1_CI_AS
10 SQLCHAR 0 0 ";" 10 ReleaseTOD Latin1_General_CP1_CI_AS
11 SQLCHAR 0 0 ";" 11 WestofGMT Latin1_General_CP1_CI_AS
12 SQLCHAR 0 0 ";" 12 RelCauseTxt Latin1_General_CP1_CI_AS
13 SQLCHAR 0 0 ";" 13 RelCauseBin Latin1_General_CP1_CI_AS
14 SQLCHAR 0 0 ";" 14 1strelease Latin1_General_CP1_CI_AS
15 SQLCHAR 0 0 ";" 15 OrgTrunkId Latin1_General_CP1_CI_AS
16 SQLCHAR 0 0 ";" 16 OrgProtocol Latin1_General_CP1_CI_AS
17 SQLCHAR 0 0 ";" 17 OrgSrcNo Latin1_General_CP1_CI_AS
18 SQLCHAR 0 0 ";" 18 OrgSrcHost Latin1_General_CP1_CI_AS
19 SQLCHAR 0 0 ";" 19 OrgDestNo Latin1_General_CP1_CI_AS
20 SQLCHAR 0 0 ";" 20 OrgDestHost Latin1_General_CP1_CI_AS
21 SQLCHAR 0 0 ";" 21 OrgCallID Latin1_General_CP1_CI_AS
22 SQLCHAR 0 0 ";" 22 OrgRemPayIPAdd Latin1_General_CP1_CI_AS
23 SQLCHAR 0 0 ";" 23 OrgRemPayUDPAdd Latin1_General_CP1_CI_AS
24 SQLCHAR 0 0 ";" 24 OrgLocPayIPAdd Latin1_General_CP1_CI_AS
25 SQLCHAR 0 0 ";" 25 OrgLocPayUDPAdd Latin1_General_CP1_CI_AS
26 SQLCHAR 0 0 ";" 26 OrgCodecList Latin1_General_CP1_CI_AS
27 SQLCHAR 0 0 ";" 27 OrgIngrPck Latin1_General_CP1_CI_AS
28 SQLCHAR 0 0 ";" 28 OrgEgrPck Latin1_General_CP1_CI_AS
29 SQLCHAR 0 0 ";" 29 OrgIngrOct Latin1_General_CP1_CI_AS
30 SQLCHAR 0 0 ";" 30 OrgEgrOct Latin1_General_CP1_CI_AS
31 SQLCHAR 0 0 ";" 31 OrgIngrPckLoss Latin1_General_CP1_CI_AS
32 SQLCHAR 0 0 ";" 32 OrgIngrDelay Latin1_General_CP1_CI_AS
33 SQLCHAR 0 0 ";" 33 OrgIngrPckJitter Latin1_General_CP1_CI_AS
34 SQLCHAR 0 0 ";" 34 TermTrunkId Latin1_General_CP1_CI_AS
35 SQLCHAR 0 0 ";" 35 TermProtocol Latin1_General_CP1_CI_AS
36 SQLCHAR 0 0 ";" 36 TermSrcNo Latin1_General_CP1_CI_AS
37 SQLCHAR 0 0 ";" 37 TermSrcHost Latin1_General_CP1_CI_AS
38 SQLCHAR 0 0 ";" 38 TermDestNo Latin1_General_CP1_CI_AS
39 SQLCHAR 0 0 ";" 39 TermDestHost Latin1_General_CP1_CI_AS
40 SQLCHAR 0 0 ";" 40 TermCallID Latin1_General_CP1_CI_AS
41 SQLCHAR 0 0 ";" 41 TermRemPayIPAdd Latin1_General_CP1_CI_AS
42 SQLCHAR 0 0 ";" 42 TermRemPayUDPAdd Latin1_General_CP1_CI_AS
43 SQLCHAR 0 0 ";" 43 TermLocPayIPAdd Latin1_General_CP1_CI_AS
44 SQLCHAR 0 0 ";" 44 TermLocPayUDPAdd Latin1_General_CP1_CI_AS
45 SQLCHAR 0 0 ";" 45 TermCodecList Latin1_General_CP1_CI_AS
46 SQLCHAR 0 0 ";" 46 TermIngrPck Latin1_General_CP1_CI_AS
47 SQLCHAR 0 0 ";" 47 TermEgrPck Latin1_General_CP1_CI_AS
48 SQLCHAR 0 0 ";" 48 TermIngrOct Latin1_General_CP1_CI_AS
49 SQLCHAR 0 0 ";" 49 TermEgrOct Latin1_General_CP1_CI_AS
50 SQLCHAR 0 0 ";" 50 TermIngrPckLoss Latin1_General_CP1_CI_AS
51 SQLCHAR 0 0 ";" 51 TermIngrDelay Latin1_General_CP1_CI_AS
52 SQLCHAR 0 0 ";" 52 TermIngrPckJitter Latin1_General_CP1_CI_AS
53 SQLCHAR 0 0 ";" 53 FinRouteInd Latin1_General_CP1_CI_AS
54 SQLCHAR 0 0 ";" 54 RoutingDigits Latin1_General_CP1_CI_AS
55 SQLCHAR 0 0 ";" 55 CallDuration Latin1_General_CP1_CI_AS
56 SQLCHAR 0 0 ";" 56 PostDialDelay Latin1_General_CP1_CI_AS
57 SQLCHAR 0 0 ";" 57 RingTime Latin1_General_CP1_CI_AS
58 SQLCHAR 0 0 ";" 58 Duration Latin1_General_CP1_CI_AS
59 SQLCHAR 0 0 ";" 59 ConfID Latin1_General_CP1_CI_AS
60 SQLCHAR 0 0 ";" 60 RPIDANI Latin1_General_CP1_CI_AS
61 SQLCHAR 0 0 "" 0 x ""
This is my actual table structure:
CREATE TABLE [dbo].[SansayRawInfo](
[RawDataId] [bigint] IDENTITY(1,1) NOT NULL,
[Sequence] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Version] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RecordType] [varchar](44) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConnectionType] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SessionID] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReleaseCause] [varchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartTime] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AnswerTime] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReleaseTOD] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WestofGMT] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RelCauseTxt] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RelCauseBin] [varchar](28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[1strelease] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgTrunkId] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgProtocol] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgSrcNo] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgSrcHost] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgDestNo] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgDestHost] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgCallID] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgRemPayIPAdd] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgRemPayUDPAdd] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgLocPayIPAdd] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgLocPayUDPAdd] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgCodecList] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgIngrPck] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgEgrPck] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgIngrOct] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgEgrOct] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgIngrPckLoss] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgIngrDelay] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgIngrPckJitter] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermTrunkId] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermProtocol] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermSrcNo] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermSrcHost] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermDestNo] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermDestHost] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermCallID] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermRemPayIPAdd] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermRemPayUDPAdd] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermLocPayIPAdd] [varchar](26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermLocPayUDPAdd] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermCodecList] [varchar](138) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermIngrPck] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermEgrPck] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermIngrOct] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermEgrOct] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermIngrPckLoss] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermIngrDelay] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TermIngrPckJitter] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinRouteInd] [varchar](44) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RoutingDigits] [varchar](74) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CallDuration] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostDialDelay] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RingTime] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Duration] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConfID] [varchar](46) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RPIDANI] [varchar](74) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [bit] NULL DEFAULT ((0))
) ON [PRIMARY]
... Thanks in advanced.