Bulk Insert with Format File

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  September 13, 2010 at 1:55 pm

  #240272

  Hi,

  Here is my Table:

  CREATE TABLE NewCDN

  (

  [Agent] char(30),

  [Target_Id] char(7),

  [Performance] char(6),

  [CountNum] char(4),

  [Availability] char(6),

  [Alias] char(55),

  [Date] datetime

  )

  Here is My Format File:

  10.0

  7

  1 SQLCHAR 0 100 "\t" 1 Agent SQL_Latin1_General_CP1_CI_AS

  2 SQLCHAR 0 100 "\t" 2 Target_Id SQL_Latin1_General_CP1_CI_AS

  3 SQLCHAR 0 100 "\t" 3 Performance SQL_Latin1_General_CP1_CI_AS

  4 SQLCHAR 0 100 "\t" 4 CountNum SQL_Latin1_General_CP1_CI_AS

  5 SQLCHAR 0 100 "\t" 5 Availability SQL_Latin1_General_CP1_CI_AS

  6 SQLCHAR 0 300 "\t" 6 Alias SQL_Latin1_General_CP1_CI_AS

  7 SQLCHAR 0 0 "\r" 7 Date SQL_Latin1_General_CP1_CI_AS

  I dont have 'Date' Column in the data file and was using this format file to insert data into the Tbale. But now it gives me "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 7 (Date).

  Msg 4864, Level 16, State 1, Line 1" Error.....can you tell me whats wrong with it?

  Thanks.

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  September 13, 2010 at 3:21 pm

  #1220326

  Just leave the date off if the date column is nullable. If it's not nullable, you should specify a default for it.

  10.0

  6

  1 SQLCHAR 0 100 "\t" 1 Agent SQL_Latin1_General_CP1_CI_AS

  2 SQLCHAR 0 100 "\t" 2 Target_Id SQL_Latin1_General_CP1_CI_AS

  3 SQLCHAR 0 100 "\t" 3 Performance SQL_Latin1_General_CP1_CI_AS

  4 SQLCHAR 0 100 "\t" 4 CountNum SQL_Latin1_General_CP1_CI_AS

  5 SQLCHAR 0 100 "\t" 5 Availability SQL_Latin1_General_CP1_CI_AS

  6 SQLCHAR 0 300 "\r" 6 Alias SQL_Latin1_General_CP1_CI_AS

  ---

  And then again, I might be wrong ...
  David Webb

• NoviceJatt

  SSCommitted

  Points: 1671

  More actions

  September 13, 2010 at 4:10 pm

  #1220351

  Hi,

  Thank you very much ...I got it right ...