BULK INSERT - Invalid destination table column number for source column.

When I do a BULK INSERT, I am getting this error.

Could not bulk insert. Invalid destination table column number for source column 1 in format file 'C:\FormatFile\ACCEB4.fmt'. [SQLSTATE 42000] (Error 4828).  The step failed.

What does it mean?

LVL 4
batchakamalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you configured the bulk insert statement incorrectly in regards to the destination table

without knowing the file structure, the destination table structure and the bulk insert
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
batchakamalAuthor Commented:
My Format File is,

8.0
12
1      SQLCHAR      0      7      """      1      Col1      Latin1_General_CI_AI
2      SQLCHAR      0      11      """      2      Col2      Latin1_General_CI_AI
3      SQLCHAR      0      28      """      3      Col3      Latin1_General_CI_AI
4      SQLCHAR      0      13      """      4      Col4      Latin1_General_CI_AI
5      SQLCHAR      0      16      """      5      Col5      Latin1_General_CI_AI
6      SQLCHAR      0      21      """      6      Col6      Latin1_General_CI_AI
7      SQLCHAR      0      26      """      7      Col7      Latin1_General_CI_AI
8      SQLCHAR      0      9      """      8      Col8      Latin1_General_CI_AI
9      SQLCHAR      0      12      """      9      Col9      Latin1_General_CI_AI
10      SQLCHAR      0      14      """      10      Col10      Latin1_General_CI_AI
11      SQLCHAR      0      9      """      11      Col11      Latin1_General_CI_AI
12      SQLCHAR      0      6      "\r\n"      12      Col12      Latin1_General_CI_AI

and My Destination Table Struture is,

CREATE TABLE [dbo].[temp_StagingTable] (
      [Col1] [varchar] (7) COLLATE NULL ,
      [Col2] [varchar] (11) COLLATE NULL ,
      [Col3] [varchar] (28) COLLATE NULL ,
      [Col4] [varchar] (13) COLLATE NULL ,
      [Col5] [varchar] (16) COLLATE NULL ,
      [Col6] [varchar] (21) COLLATE NULL ,
      [Col7] [varchar] (26) COLLATE NULL ,
      [Col8] [varchar] (9) COLLATE NULL ,
      [Col9] [varchar] (12) COLLATE NULL ,
      [Col10] [varchar] (14) COLLATE NULL ,
      [Col11] [varchar] (9) COLLATE NULL ,
      [Col12] [varchar] (6) COLLATE NULL
) ON [PRIMARY]
GO

and My Sample data is

     1 A1         8286883388901               4752.00      100.00          086752026            03-nov-2004               EXTR82   08-dec-2003 00:08:59      SAR      100.00
     1 A1         9515876428012               4696.00      100.00          085634026            02-nov-2004               EXTR84   08-dec-2003 21:30:48      SAR      100.00
     1 A1         8524193759146               5067.00      100.00          092934147            30-nov-2004               EXTR126  20-dec-2003 20:41:50      SAR      100.00

0
batchakamalAuthor Commented:
Correction in Table Structure,

CREATE TABLE [dbo].[temp_StagingTable] (
      [Col1] [varchar] (7)  NULL ,
      [Col2] [varchar] (11)  NULL ,
      [Col3] [varchar] (28)  NULL ,
      [Col4] [varchar] (13)  NULL ,
      [Col5] [varchar] (16)  NULL ,
      [Col6] [varchar] (21)  NULL ,
      [Col7] [varchar] (26)  NULL ,
      [Col8] [varchar] (9)  NULL ,
      [Col9] [varchar] (12)  NULL ,
      [Col10] [varchar] (14)  NULL ,
      [Col11] [varchar] (9)  NULL ,
      [Col12] [varchar] (6)  NULL
) ON [PRIMARY]
GO
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

batchakamalAuthor Commented:
Sorry ...
The problem is with my Format file, the column delimeter is space, so it is supposed to be "" not """".
Not it is working fine.
Thanks angelIII
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please change the format file to this:


8.0
12
1     SQLCHAR     0     7     ""     1     Col1     Latin1_General_CI_AI
2     SQLCHAR     0     11     ""     2     Col2     Latin1_General_CI_AI
3     SQLCHAR     0     28     ""     3     Col3     Latin1_General_CI_AI
4     SQLCHAR     0     13     ""     4     Col4     Latin1_General_CI_AI
5     SQLCHAR     0     16     ""     5     Col5     Latin1_General_CI_AI
6     SQLCHAR     0     21     ""     6     Col6     Latin1_General_CI_AI
7     SQLCHAR     0     26     ""     7     Col7     Latin1_General_CI_AI
8     SQLCHAR     0     9     ""     8     Col8     Latin1_General_CI_AI
9     SQLCHAR     0     12     ""     9     Col9     Latin1_General_CI_AI
10     SQLCHAR     0     14     ""     10     Col10     Latin1_General_CI_AI
11     SQLCHAR     0     9     ""     11     Col11     Latin1_General_CI_AI
12     SQLCHAR     0     6     "\r\n"     12     Col12     Latin1_General_CI_AI
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see, meanwhile you found it yourself
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.