Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1935
  • Last Modified:

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?

0
batchakamal
Asked:
batchakamal
  • 3
  • 3
1 Solution
 
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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now