We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

batchakamal
batchakamal asked
on
Medium Priority
2,603 Views
Last Modified: 2010-08-05
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?

Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
batchakamalAdvisor

Author

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

batchakamalAdvisor

Author

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
batchakamalAdvisor

Author

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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I see, meanwhile you found it yourself
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.